Need Access VBA similar to the given Excel VBA

[JFKJr]

Hi, the following Excel VBA code is used to select 5 rows by double clicking the cell in the first column corresponding to that row. The code is working fine in excel.

But, I need Access VBA code, which opens the excel file and performs the same function.

Can anyone suggest me how to do this? Thanks in advance!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ColRange As String, PoliciesCount As Integer, LastUsedRow As Long
If Target.Count > 1 Then Exit Sub
 
If WorksheetFunction.CountA(Cells) > 0 Then
LastUsedRow = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
End If
 
ColRange = "A2:A" & LastUsedRow
If Intersect(Target, Range(ColRange)) Is Nothing Then Exit Sub
Target.Font.Name = "marlett"
 
If Target.Value <> "a" Then
PoliciesCount = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
    If PoliciesCount > 5 Then
        MsgBox "You cannot select more than 5 rows", vbOKOnly Or vbCritical, "Warning!"
        Exit Sub
    End If
    Target.Value = "a"
    Cancel = True
    Exit Sub
End If
 
If Target.Value = "a" Then
    Target.ClearContents
    Cancel = True
    Exit Sub
End If
End Sub

[puppydogbuddy]

Ok, got it. Add the Do Loop as shown:

Do until Target.Value = "a"
'Worksheet_SelectionChange Target
Worksheet_Activate
Loop

[JFKJr]

Ok, got it. Add the Do Loop as shown:

Do until Target.Value = "a"
'Worksheet_SelectionChange Target
Worksheet_Activate
Loop

I replaced the code as above and it is giving me same "Object Required error" at line #15:

Sub RunMacro()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
 
With xlApp
.Visible = True
.Interactive = True
Set xlWB = .Workbooks.Open("C:\ExtractPolicyList2.xls", , False)
xlWB.Worksheets(1).Activate
xlWB.ActiveSheet.Range("a5").Select
MsgBox "" & ActiveCell
End With
 
Do Until Target.Value = "a"
'Worksheet_SelectionChange Target
Worksheet_Activate
Loop
 
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

Try changing : Do Until Target.Value = "a"

to: Do Until Target = "a"

or to: Do Until xlWB.Target.Value = "a"

or to: Do Until ActiveCell.Value = "a"

[JFKJr]

Try changing : Do Until Target.Value = "a"

to: Do Until Target = "a"

or to: Do Until xlWB.Target.Value = "a"

or to: Do Until ActiveCell.Value = "a"

I replaced the "Do Until" loop with the following code

Do Until Target = "a"
Worksheet_SelectionChange Target
Loop

=> generates "Object Required" error at line #1

Do Until xlWB.Target.Value = "a"
Worksheet_SelectionChange Target
Loop

=> generates "Run-time error '438': Object doesn't support this property or method" error at line#1

Do Until ActiveCell.Value = "a"
Worksheet_SelectionChange Target
Loop

=> generates "Object Required" error at line #2

Do Until ActiveCell.Value = "a"
Worksheet_SelectionChange ActiveCell
Loop

=>calls "Worksheet_SelectionChange" event only one time when I run "RunMacro()" proc and coming out of the loop.

[puppydogbuddy]

Try:

Do Until xlWB.Worksheets(1).Target.Value ="a"
Worksheet_SelectionChange Target
Loop

[JFKJr]

Try:

Do Until xlWB.Worksheets(1).Target.Value ="a"
Worksheet_SelectionChange Target
Loop

The above code is generating "Run-time error '438': Object doesn't support this property or method" error at line#1.

[puppydogbuddy]

#$%^&*$@ (just kidding!)

try changing this:
Do Until xlWB.Worksheets(1).Target.Value ="a"


To:
Do Until xlWB.Worksheets(1).ActiveCell.Value ="a"

[JFKJr]

#$%^&*$@ (just kidding!)

try changing this:
Do Until xlWB.Worksheets(1).Target.Value ="a"


To:
Do Until xlWB.Worksheets(1).ActiveCell.Value ="a"

It is generating same "Run-time error '438': Object doesn't support this property or method" error.

I have searched internet a loooooooooot but unable to find anything on "handling excel events in access VBA" :(. And, now I am doubting that does anyone ever faced this problem before or I am the only person in the earth who is having this kind of ugly problem :(

[puppydogbuddy]

It is generating same "Run-time error '438': Object doesn't support this property or method" error.

I have searched internet a loooooooooot but unable to find anything on handling excel events in access VBA :(. And, now I am doubting that does anyone faced this problem before or I am the only person in the earth who is having this kind of ugly problem :(

I have used excel vba before and never had this problem, but I did not have a click on cell type of app either. Were you able to run the complete tutorial app that I sent you.(Kaleidescope) ? That would be a good test.

If the code below does not work, its time to ask Mr. Excel!!!

http://www.mrexcel.com/archive/VBA/index.html

Do Until ActiveWorksheet.Cells.Range(Target) = "a"

or

Do Until ActiveWorksheet.Cells(ActiveCell.Value) = "a"

[JFKJr]

I have used excel vba before and never had this problem, but I did not have a click on cell type of app either. Were you able to run the complete tutorial app that I sent you.(Kaleidescope) ? That would be a good test.

If the code below does not work, its time to ask Mr. Excel!!!

http://www.mrexcel.com/archive/VBA/index.html

Do Until ActiveWorksheet.Cells.Range(Target) = "a"

or

Do Until ActiveWorksheet.Cells(ActiveCell.Value) = "a"

I am not sure what is Kaleidescope means, but yes I was able to run the complete code in the tutorial you sent -
http://pubs.logicalexpressions.com/P...cle.asp?ID=407

As I said before, with the code in the tutorial I was able to run the events in excel VBA whenever the action corresponding to it happens. But in contrary to excel, the access VBA is unable to run events whenever the action corresponding to it happens.

And regarding the above code, it is giving again the same "Object Required" error for both cases.

Anyways, thanks for all your patience, time and help :) Thank You very much.

I will try to contact Mr. Excel and see how it goes...

Thanks a lot!

[puppydogbuddy]

I am not giving up yet....I am going to research it on my own and get back to you if I find anything. The different shapes and colors generated by the tutorial create a Kaleidescope.

[JFKJr]

I am not giving up yet....I am going to research it on my own and get back to you if I find anything. The different shapes and colors generated by the tutorial create a Kaleidescope.

Yes, I have even tested Kaleidoscope in the tutorial and thanks a lot for all the help you did and for continuing your research on the problem.

I will keep in touch with the post and will update the status if any progress!

Thanks!

[puppydogbuddy]

I think this may be part of the answer...the SelectionChange event is not activated when the change occurs with vba code, only by selection. So try changing the event to the worksheetChange event instead.

Change this:

Do Until ActiveCell.Value = "a"
Worksheet_SelectionChange Target
Loop

to this:

Do Until ActiveCell.Value = "a"
Worksheet_Change Target
Loop

Don't for to change the name of the Worksheet_SelectionChange sub to Worksheet_Change as well.

Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        Worksheet_BeforeDoubleClick Target.Value           
        Target.Value = Target.Value + 1                       'may need to comment this line out         
        Application.EnableEvents = True
End Sub

Hope this helps.....let me know.

[JFKJr]

I think this may be part of the answer...the SelectionChange event is not activated when the change occurs with vba code, only by selection. So try changing the event to the worksheetChange event instead.

Change this:

Do Until ActiveCell.Value = "a"
Worksheet_SelectionChange Target
Loop

to this:

Do Until ActiveCell.Value = "a"
Worksheet_Change Target
Loop

Don't for to change the name of the Worksheet_SelectionChange sub to Worksheet_Change as well.

Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
        Worksheet_BeforeDoubleClick Target.Value           
        Target.Value = Target.Value + 1                       'may need to comment this line out         
        Application.EnableEvents = True
End Sub

Hope this helps.....let me know.

Hello puppydogbuddy, sorry for the late response..

The following is the code with the above changes and it is generating "Object Required" error at line #14.

Sub RunMacro()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
 
With xlApp
.Visible = True
.Interactive = True
Set xlWB = .Workbooks.Open("C:\ExtractPolicyList2.xls", , False)
xlWB.Worksheets(1).Activate
xlWB.ActiveSheet.Range("a5").Select
MsgBox "" & ActiveCell
Do Until ActiveCell.Value = "a"
Worksheet_Change Target
Loop
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheet_BeforeDoubleClick Target, False
'Target.Value = Target.Value + 1   'may need to comment this lineout
Application.EnableEvents = True
End Sub

So, I changed the above 14th line to the following line and it is giving "Method or Data Member not found" error at line #2 in "Worksheet_Change" proc.

Worksheet_Change ActiveCell

I also tried the following code by changing a little bit and the code is selecting the "A5" cell and coming out of the loop. The code is not automatically calling "Worksheet_Change" proc, which in turn calls "Worksheet_BeforeDoubleClick " proc, whenever I double click a cell.

Sub RunMacro()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
 
With xlApp
.Visible = True
.Interactive = True
Set xlWB = .Workbooks.Open("C:\Users\Harika\Documents\ExtractPolicyList2.xls", , False)
xlWB.Worksheets(1).Activate
xlWB.ActiveSheet.Range("a5").Select
MsgBox "" & ActiveCell
Do Until ActiveCell.Value = "a"
 xlWB.Application.EnableEvents = False
Worksheet_Change ActiveCell
 xlWB.Application.EnableEvents = True
Loop
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_BeforeDoubleClick Target, False
'Target.Value = Target.Value + 1   'may need to comment this line out
End Sub

FYI, I submitted a post in Mr. Excel and somebody gave me a suggestion to include "Do Events".
Please kindly have a look at the post:
http://www.mrexcel.com/forum/showthread.php?p=1636778

[Stewart Ross]

Hi again guys; sorry you are having such a struggle with this one. If we could make an award for dogged persistence you would both win it! Thanks PDog for all your assistance with this post (a thread that has become an epic!) and JFKjr for your patience despite what must now be great frustration.

In my opinion it might be better to rethink what it is you want to achieve for your users, then implement the effects (column width changes and so on) directly in the Access VBA code instead of trying to call Excel event handlers. It is no problem to change column widths, fonts, and so on in code from Access.

The Mr Excel reply points up the use of Do Events - but this can only be part of the answer. For Access to respond to Excel events you will need to declare the Excel object using the WithEvents qualifier so that Access can 'listen' for the events raised by Excel. I think you would still need to replicate the event code in Access for the worksheet change event for instance. I haven't used Excel events myself; I have however devised bespoke event handlers in Access for class modules I developed. I found that setting the applications up to send and listen to events was more challenging than actually responding to the events themselves, which is beautifully simple once the event sender and handlers are set up and listening to each other...

Your object errors that have arisen again and again result as previously mentioned from lack of qualification of the application object. The event errors seem to me to be occurring because the application is not yet set to listen to the events.

An extract from the Excel help file is listed below.

Personally, I would step back and consider why you need to respond to worksheet events at all; if you simply need to format a range of cells dynamically why not do so directly from Access??

-Stewart

Using Events with the Application Object

Before you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

Public WithEvents App As Application

After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module.

Dim X As New EventClassModuleSub InitializeApp()
Set X.App = Application
End Sub

After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur.

[puppydogbuddy]

Stewart,
Thanks to the info you provided, I think we have the basis for a solution. There is more than one way to skin a cat, and I finally found an authoritative source on how to make this work with the Do Until Loop (instead of event handlers), I suggested earlier, but did not know there was a special way to code it in vba.

See this link(excerpted below):
http://support.microsoft.com/default...b;en-us;141762

 ' Performs Do loop, testing at top of loop.
   ' Loops until empty cell is reached.
   ' Note that you can also use a Do While Not IsEmpty(ActiveCell) loop.
 
   Sub test_before_do_loop()
 
      ' Test contents of active cell; if active cell is empty, exit loop.
      Do Until IsEmpty(ActiveCell)
 
         ' Displays cell contents in message box.
         MsgBox ActiveCell.Value
 
         ' Step down 1 row to the next cell.
         ActiveCell.Offset(1, 0).Select
 
        ' Return to top of loop.
      Loop
   End Sub

[puppydogbuddy]

Based on my interpretation of the ms kb article, this should work:

Sub RunMacro()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
 
With xlApp
.Visible = True
.Interactive = True
Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)                                 
xlWB.Worksheets(1).Activate
xlWB.ActiveSheet.Range("a5").Select
' Performs Do loop, testing at top of loop.
 
       ' Test contents of active cell;
      Do Until ActiveCell.Value = "a"
 
         ' Displays cell contents in message box.
         MsgBox ActiveCell.Value
 
         Worksheet_BeforeDoubleClick ActiveCell, False
 
         ' Step down 1 row to the next cell.
       '  ActiveCell.Offset(1, 0).Select ..............commented out for now<<<<<
 
        ' Return to top of loop.
      Loop
   End Sub
 
End With
 
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

If there is still an interest vba event handlers for Excel, I found this authoritative reference on how to enable, fire, and disable event handlers using vba.

http://support.microsoft.com/default...b;en-us;165867

[JFKJr]

If there is still an interest vba event handlers for Excel, I found this authoritative reference on how to enable, fire, and disable event handlers using vba.

http://support.microsoft.com/default...b;en-us;165867

Hello Puppydogbuddy and Stewart, first of all thanks a loooooooooooot for all your help and ideas on the post.

I am successfully able to make the code working by including "WithEvents", which handles excel events in Access.

The following are the steps I followed to open an excel file from Access VBA and call "BeforeDoubleClick" excel event whenever a user double clicks on a cell:

1. Created a new class module called "clsExcel" and defined the excel events, which I would like to use with the excel.

Public WithEvents AppExcel As Excel.Application
Public WithEvents AppWB As Excel.Workbook
Public WithEvents AppWS As Excel.Worksheet
 
Private Sub AppExcel_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
With AppExcel.Workbooks
AppExcel.Quit
Set clsExcelApp.AppExcel = Nothing
Set clsExcelApp.AppWB = Nothing
Set clsExcelApp.AppWS = Nothing
Set clsExcelApp = Nothing
End With
End Sub
 
Private Sub AppWS_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim ColRange As String, LastUsedRow As Long, PoliciesCount As Integer
With AppWS
If Target.Count > 1 Then Exit Sub
 
If WorksheetFunction.CountA(Cells) > 0 Then
   LastUsedRow = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
End If
 
ColRange = "A2:A" & LastUsedRow
If Intersect(Target, Range(ColRange)) Is Nothing Then Exit Sub
Target.Font.Name = "marlett"
If Target.Value <> "a" Then
PoliciesCount = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
    If PoliciesCount > 5 Then
        MsgBox "You cannot select more than 5 rows", vbOKOnly Or vbCritical, "Warning!"
        Exit Sub
    End If
    Target.Value = "a"
    Cancel = True
    Exit Sub
End If
If Target.Value = "a" Then
   Target.ClearContents
   Cancel = True
   Exit Sub
End If
End With
End Sub

2. Created a standard module and instantiated the class "clsExcel" created above with a current Application Object:

Public clsExcelApp As New clsExcel
 
Sub RunMacro()
With clsExcelApp
If .AppExcel Is Nothing Then Set .AppExcel = New Excel.Application
'show the Application
.AppExcel.Visible = True
.AppExcel.Interactive = True
Set .AppWB = .AppExcel.Workbooks.Open("C:\Users\Harika\Documents\ExtractPolicyList2.xls", , False)
Set .AppWS = .AppWB.Worksheets(1)
End With
End Sub

3. And when I run the "RunMacro()" defined above, the code is opening the excel file and calling "BeforeDoubleClick" event whenever I double click a cell (wonderful, can you believe it!)

Stewart, thanks a lot for the idea on "WithEvents", which is working like a charm in handling excel events with access.

puppydogbuddy, thank you very very much for your most valuable time, ideas and help on the post. I am a new beginner to Access & Excel VBA, but with your ideas and the article links you provided on this post I think I learnt most VBA :). Thank You!

And, I am officially declaring the successfull end of this thread. Cheers :)

Please kindly let me know if any questions. Thanks!

[JFKJr]

Hello Puppydogbuddy and Stewart, first of all thanks a loooooooooooot for all your help and ideas on the post.

I am successfully able to make the code working by including "WithEvents", which handles excel events in Access.

The following are the steps I followed to open an excel file from Access VBA and call "BeforeDoubleClick" excel event whenever a user double clicks on a cell:

1. Created a new class module called "clsExcel" and defined the excel events, which I would like to use with the excel.

Public WithEvents AppExcel As Excel.Application
Public WithEvents AppWB As Excel.Workbook
Public WithEvents AppWS As Excel.Worksheet
 
Private Sub AppExcel_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
With AppExcel.Workbooks
AppExcel.Quit
Set clsExcelApp.AppExcel = Nothing
Set clsExcelApp.AppWB = Nothing
Set clsExcelApp.AppWS = Nothing
Set clsExcelApp = Nothing
End With
End Sub
 
Private Sub AppWS_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim ColRange As String, LastUsedRow As Long, PoliciesCount As Integer
With AppWS
If Target.Count > 1 Then Exit Sub
 
If WorksheetFunction.CountA(Cells) > 0 Then
   LastUsedRow = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
End If
 
ColRange = "A2:A" & LastUsedRow
If Intersect(Target, Range(ColRange)) Is Nothing Then Exit Sub
Target.Font.Name = "marlett"
If Target.Value <> "a" Then
PoliciesCount = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
    If PoliciesCount > 5 Then
        MsgBox "You cannot select more than 5 rows", vbOKOnly Or vbCritical, "Warning!"
        Exit Sub
    End If
    Target.Value = "a"
    Cancel = True
    Exit Sub
End If
If Target.Value = "a" Then
   Target.ClearContents
   Cancel = True
   Exit Sub
End If
End With
End Sub

2. Created a standard module and instantiated the class "clsExcel" created above with a current Application Object:

Public clsExcelApp As New clsExcel
 
Sub RunMacro()
With clsExcelApp
If .AppExcel Is Nothing Then Set .AppExcel = New Excel.Application
'show the Application
.AppExcel.Visible = True
.AppExcel.Interactive = True
Set .AppWB = .AppExcel.Workbooks.Open("C:\Users\Harika\Documents\ExtractPolicyList2.xls", , False)
Set .AppWS = .AppWB.Worksheets(1)
End With
End Sub

3. And when I run the "RunMacro()" defined above, the code is opening the excel file and calling "BeforeDoubleClick" event whenever I double click a cell (wonderful, can you believe it!)

Stewart, thanks a lot for the idea on "WithEvents", which is working like a charm in handling excel events with access.

puppydogbuddy, thank you very very much for your most valuable time, ideas and help on the post. I am a new beginner to Access & Excel VBA, but with your ideas and the article links you provided on this post I think I learnt most VBA :). Thank You!

And, I am officially declaring the successfull end of this thread. Cheers :)

Please kindly let me know if any questions. Thanks!

Hello guys, I am having a small issue when I run the above code, I know it got to do something with the application reference, but I am not sure how to do it.

When I run "RunMacro()" proc above, it is opening an excel file and when I double click on any cell it is calling "AppWS_BeforeDoubleClick" event.

The above code is working fine for the first time and generating "Method 'Cells' of object '_Global' failed" error at line #20 for the second time and so on..(the code is working sometimes and sometimes not).

Please kindly let me know what to do? Thanks!

[puppydogbuddy]

Hello guys, I am having a small issue when I run the above code, I know it got to do something with the application reference, but I am not sure how to do it.

When I run "RunMacro()" proc above, it is opening an excel file and when I double click on any cell it is calling "AppWS_BeforeDoubleClick" event.

The above code is working fine for the first time and generating "Method 'Cells' of object '_Global' failed" error at line #20 for the second time and so on..(the code is working sometimes and sometimes not).

Please kindly let me know what to do? Thanks!

Try changing this:

CountA(Cells())

To:

CountA(Range("A1:D500")) .......replace A1:D500 with whatever range you want

[JFKJr]

Try changing this:

CountA(Cells())

To:

CountA(Range("A1:D500")) .......replace A1:D500 with whatever range you want

Hello Puppudogbuddy, thanks for the response, nice to see you again :-)

Unfortunately it is giving the same error. Let me simplify my problem by concentrating only on the error occuring code:

The following is the code opens the excel file and displays the last used row in the excel. The code is working fine for the first time and giving "Run-time error '13': Type mismatch" at line #15 during the second time and so on....

The code is working sometimes and sometimes not!

Please kindly let me know what to do?

Sub RunMacro()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim LastUsedRow As Long
Set xlApp = New Excel.Application
 
With xlApp
.Visible = True
.Interactive = True
Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
Set xlWS = xlWB.Worksheets("SelectPolicies")
 
If .WorksheetFunction.CountA(xlWS.Cells) > 0 Then
  LastUsedRow = xlWS.Cells.Find(What:="*", After:=[A1], _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row
  MsgBox "" & LastUsedRow
End If
End With
End Sub
 

[puppydogbuddy]

Hi JFKJr,

This might be the problem.....the WorkSheetFunction is a method of the application object, so try this"

If Application.WorksheetFunction.CountA(xlWS.Cells) > 0

[JFKJr]

Hi JFKJr,

This might be the problem.....the WorkSheetFunction is a method of the application object, so try this"

If Application.WorksheetFunction.CountA(xlWS.Cells) > 0

Yes, you are right the "WorkSheetFunction" is a method of the application object. That is the reason I used:

"With xlApp" (application object) at line #8 above

And," .WorksheetFunction" at line #14 above (used "." to explicitly refer the method to the "xlApp" application object).

By the way, the error is occuring at line #15 and it is not giving any error for " .WorksheetFunction" at line #14.

The error is occurring definitely because of not explicitly referring the application/cells object. But I am not sure how to solve it.

[JFKJr]

Yes, you are right the "WorkSheetFunction" is a method of the application object. That is the reason I used:

"With xlApp" (application object) at line #8 above

And," .WorksheetFunction" at line #14 above (used "." to explicitly refer the method to the "xlApp" application object).

By the way, the error is occuring at line #15 and it is not giving any error for " .WorksheetFunction" at line #14.

The error is occurring definitely because of not explicitly referring the application/cells object. But I am not sure how to solve it.

I solved the issue, I changed the 15 line above to the following and it worked.

LastUsedRow = .Cells.Find(what:="*", after:=.Cells(1, 1), lookat:=xlPart, searchorder:=xlByRows, SearchDirection:=xlPrevious).Row

Thanks anyway though!

[puppydogbuddy]

I just got back on-line and see that you resolved your problem. That is great. Best of luck with your application.

[Stewart Ross]

Excellent work both! I'm really glad you have resolved the events and the final glitches. The last one was a good example of why explicit qualification of the Excel application is needed - sometimes implicit references work (on first pass usually) - then refuse to work from there on. This kind of behaviour is really confusing when debugging, because it misleads you into thinking the algorithm implementation is incorrect, when in fact this is fine all along.

Glad PDog could assist you, and that my smaller contribution also assisted.

Cheers

Stewart