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]

Your Excel code should work fine in MS Access provided that you:
1. set a reference to the Excel Object Library in the Visual Basic Editor in MS Access.
2. use excel automation code to open your workbook from ms access (see code below) before you call your function.

To set a reference,

Open the Access VBE (ALT + F11)
Go to Tools|References
Check the box next to the relevant Excel Library (for example Microsoft Excel 9.0 Object Library if using Office 2000)
Click OK

     ' opens the specified Spreadsheet
    Dim xlApp As Excel.Application 
    Dim xlWB As Excel.Workbook 
    Set xlApp = New Excel.Application 
    With xlApp 
        .Visible = True 
        Set xlWB = .Workbooks.Open("C:\Dir_path\xlfilename.xls", , False) 
    End With 

[JFKJr]

Your Excel code should work fine in MS Access provided that you:
1. set a reference to the Excel Object Library in the Visual Basic Editor in MS Access.
2. use excel automation code to open your workbook from ms access (see code below) before you call your function.

To set a reference,

Open the Access VBE (ALT + F11)
Go to Tools|References
Check the box next to the relevant Excel Library (for example Microsoft Excel 9.0 Object Library if using Office 2000)
Click OK

     ' opens the specified Spreadsheet
    Dim xlApp As Excel.Application 
    Dim xlWB As Excel.Workbook 
    Set xlApp = New Excel.Application 
    With xlApp 
        .Visible = True 
        Set xlWB = .Workbooks.Open("C:\Dir_path\xlfilename.xls", , False) 
    End With 

Hello puppydogbuddy, first of all thank you very much for the response.

I did whatever you asked me to do, I set the reference to the Microsoft Excel 11.0 Object Library (I am using MS Access 2003) and I changed the code to the following, but I am unable to run the code could you please tell me what is the problem with the code.

I would like my code to open the spreadsheet and able to check/select 5 rows by double clicking the cell in the first column corresponding to that row (just like the way I did using excel VBE)

I would really appreciate your help. Thanks a million!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
Dim ColRange As String, PoliciesCount As Integer, LastUsedRow As Long
 
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    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

[puppydogbuddy]

When you say the code doesn't run, you need to provide some details. Do you get any compile or runtime errors? If the answer is yes, what is the error message and what lines of code are highlighted for the error? In case you don't know how to get the Code editor to highlight the error line, here's how: Go to the command menu for the VB code editor and select Tools>options>general tab>checkmark "break on all errors". Be sure to change it back to"break on unhandled errors when you are finished debugging.

If you don't get any errors, but program does not execute, your problem could be a security issue related to the sandbox security defaults implemented by Microsoft in 2003. To fix, go to the Access (not VB) command menu, and set your macro security level to low.

[JFKJr]

When you say the code doesn't run, you need to provide some details. Do you get any compile or runtime errors? If the answer is yes, what is the error message and what lines of code are highlighted for the error? In case you don't know how to get the Code editor to highlight the error line, here's how: Go to the command menu for the VB code editor and select Tools>options>general tab>checkmark "break on all errors". Be sure to change it back to"break on unhandled errors when you are finished debugging.

If you don't get any errors, but program does not execute, your problem could be a security issue related to the sandbox security defaults implemented by Microsoft in 2003. To fix, go to the Access (not VB) command menu, and set your macro security level to low.

Sorry to confuse you. I have changed the code a little bit. Let me give you a detail description of my problem.

When I run the following "RunMacro()" procedure, it opens the excel file and calls "Worksheet_BeforeDoubleClick" function. Since, I am sending "A3" as a range parameter, the code is automatically selecting the 3rd row by keeping a checkmark/tick into the cell in the 1st column corresponding to the 3rd row.

But, I would like my code to open an excel file, allow the user to double click a cell and when the user double click a cell the "Worksheet_BeforeDoubleClick" function should be called, which should take the range parameter as the name of the cell clicked.

I am not sure if I explained my problem in detail or created more confusion. Please let me know if you are unable to understand my problem, I will definitely come up with better words.

Thanks a lot for your valuable time and appreciate your help. Thank you very much :)

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_BeforeDoubleClick .Range("A3"), False
End With
End Sub

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,
That was an excellent explanation.

Try this:
Target.Value = Active.Cell.Address

instead of your a3 parameter

Let me know what happens.

[JFKJr]

Ok,
That was an excellent explanation.

Try this:
Target.Value = Active.Cell.Address

instead of your a3 parameter

Let me know what happens.

Thanks for the quick response, I changed the code as the following and it is showing "Run-time error '424': Object required"error at line #9.

Please kindly let me know what to do? Thanks.

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_BeforeDoubleClick .Range(Target.Value = Active.Cell.Address), False
End With
End Sub

Note:
No changes in "Worksheet_BeforeDoubleClick" function.

[puppydogbuddy]

My fault.

Change this:
.Range(Target.Value = Active.Cell.Address)

to this:
Change this:
.Range(Target.Value = ActiveCell.Address)

However, in order to get this to work the way you want, you may have to leave "a3" as the target range in your calling proc RunMacro. Then somewhere in your called proc (on line 21??), put Target.Value = ActiveCell.Address.

Also, shouldn't your if statement following line 21 be.....if Target.Value <> "a" rather then ="a" as you have it??

Let me know.

[JFKJr]

My fault.

Change this:
.Range(Target.Value = Active.Cell.Address)

to this:
Change this:
.Range(Target.Value = ActiveCell.Address)

However, in order to get this to work the way you want, you may have to leave "a3" as the target range in your calling proc RunMacro. Then somewhere in your called proc (on line 21??), put Target.Value = ActiveCell.Address.

Also, shouldn't your if statement following line 21 be.....if Target.Value <> "a" rather then ="a" as you have it??

Let me know.

Yes, I even tried with "Target.Value = ActiveCell.Address" just like you suggested but it is giving the same run-time error at line #9 in "RunMacro()" proc.

And, regarding the line #21 in called proc:

let us assume the user clicked a cell (lets say 'A3') in an excel sheet, then the font of that cell will change into "marlett" (as per in line #14).

And, if there is no value in that cell, it assigns "a" to it (as per line #21). The reason I changed the value to "a" is, in marlett font the value "a" gives checkmark/ tick sign, which makes the user to think that he/she has selected/checked that row.

In similar way, if the user double clicks again on the cell which has value "a", the code clear the contents (as per line #26) giving the user a feeling that they have unchecked the row.

My main problem is, how to call "Worksheet_BeforeDoubleClick" function when a user clicks a cell in excel sheet?
And, how to assign the name of the clicked cell to the range parameter of the function?

I am having a very hard time to figure out how to deal with the problem. Your help on this will be greatly appreciated!

[puppydogbuddy]

Ok, now i am curious! I will do some research and get back to you tomorrow.

[JFKJr]

Ok, now i am curious! I will do some research and get back to you tomorrow.

Sure :), thanks for your time. Have a great day!

I will keep in touch with the post.

[Stewart Ross]

Hi puppydogbuddy and jfkjr. The object error in this case is occurring because the Excel application object, though defined, is not being referenced when the worksheet event handler is being called in line 9. When Excel is running as an automation server from Access the application object involved must be explicitly referenced - there can be no implied active workbook as there can be when code is run within Excel itself (the code is actually running in an Access instance, hence the need to refer through the defined Excel object instead).

To refer to the properties of the Excel application you need to do so through the xlApp object itself. This applies to all occurrences - where you are referencing the current workbook, or the active worksheet, or a range object, or the active cell etc. Examples:

something = xlApp.activeCell
with xlApp
.worksheets(1).cells(1,1) = somevalue
.activesheet.select
end with

I have not myself run code in an Excel worksheet from Excel running as an Access automation server (the point at which your error occurs). I run all my Excel code from the automation instance itself via Access - but assuming that there is no problem in doing so it will still need to be referenced through the application object if it is to run at all.

I do suspect that to run the code in the active worksheet you will need to qualify the application reference to be explicit about which sheet you are referring to but I have not tried this myself so I do not know the syntax (xlApp.ActiveSheet.worksheet_beforedoubleclick perhaps?)

-Stewart

[puppydogbuddy]

Hi Stewart,
Thanks for that info. It is definitely part of the problem...unfortunately there are other idiosyncracies that need to be addressed. Hope to have it resolved tomorrow. Congrats on the great job you are doing as moderator.

pDog

[puppydogbuddy]

Try this and let me know what happens. Note the initial range assignment to "a3" to set focus on the worksheet, and note the use of a selectionChange event to trigger the worksheet_beforeDoubleClick event .

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
 
Set xlApp = New Excel.Application
 
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_SelectionChange.Range(Target=:"a3"), False
End With 
End Sub

__________________________________________________ __

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address <> Target.Address Then
       Exit Sub
    Else
    Worksheet_BeforeDoubleClick.Range(Target.Value = xlApp.ActiveCell.Address), False    
    End If
End Sub

[JFKJr]

Try this and let me know what happens. Note the initial range assignment to "a3" to set focus on the worksheet, and note the use of a selectionChange event to trigger the worksheet_beforeDoubleClick event .

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
 
Set xlApp = New Excel.Application
 
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_SelectionChange.Range(Target=:"a3"), False
End With 
End Sub

__________________________________________________ __

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address <> Target.Address Then
       Exit Sub
    Else
    Worksheet_BeforeDoubleClick.Range(Target.Value = xlApp.ActiveCell.Address), False    
    End If
End Sub

Hello puppydogbuddy, I have tried your code, but it is giving me "Run-time error 424: Object Required" error on line #5 in "Worksheet_SelectionChange" proc.

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
 
Set xlApp = New Excel.Application
 
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_SelectionChange .Range("A3")
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Address <> Target.Address Then
     Exit Sub
    Else
    Worksheet_BeforeDoubleClick Range(Target.Value = xlApp.ActiveCell.Address), False
    End If
End Sub

Please note that I have changed the following code

Worksheet_SelectionChange.Range(Target=:"a3"), False

to

Worksheet_SelectionChange .Range("A3")

B'coz, the function is having only one argument.

[puppydogbuddy]

I know the use of ActiveCell.Address is ok, so it has to be the parent object reference, or maybe the syntax of other portions of the code on line 5. Try several different variations of the code for line 5 and see if you can get the syntax to work. For example, try the variations below and let me know what happens:

Worksheet_BeforeDoubleClick Range(Target = xlApp.ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(Target = xlApp.xlWB.ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(Target = xlWB.Sheet(1).ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(xlWB.Sheet(1).ActiveCell.Address), False

[JFKJr]

I know the use of ActiveCell.Address is ok, so it has to be the parent object reference, or maybe the syntax of other portions of the code on line 5. Try several different variations of the code for line 5 and see if you can get the syntax to work. For example, try the variations below and let me know what happens:

Worksheet_BeforeDoubleClick Range(Target = xlApp.ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(Target = xlApp.xlWB.ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(Target = xlWB.Sheet(1).ActiveCell.Address), False

Worksheet_BeforeDoubleClick Range(xlWB.Sheet(1).ActiveCell.Address), False

Hello puppydogbuddy, I have tried all the given variations but it is giving the same error.

So, I changed the code to the following and I found something interesting, may be this give you an idea on how to deal with the problem.

This is what I did:
1. Opened the excel file, activated a cell (say A5), saved and closed the file.
2. Run "RunMacro()" proc:
a) for the first time, the proc called automatically "Worksheet_BeforeDoubleClick" function and checked/ticked the cell in the first column corresponding to the 5th row.
b) for the second time, it is giving "Run-time error 91: Object variable or With block variable not set" error.

the code is performing the above tasks alternatively (i.e, sometimes working and sometimes not)

I am not understanding what to do, I am stuck with this!
Hope you get some idea on the problem!

Sub RunMacro()
' opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
 
Set xlApp = New Excel.Application
 
With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\ExtractPolicyList.xls", , False)
    Worksheet_BeforeDoubleClick .Range(ActiveCell.Address), False
End With
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ColRange As String, LastUsedRow As Long
 
If Target.Count > 1 Then Exit Sub
'MsgBox "" & Target.Count
 
If WorksheetFunction.CountA(Cells) > 0 Then
   LastUsedRow = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
    'MsgBox "" & LastUsedRow
End If
 
ColRange = "A2:A" & LastUsedRow
 
'MsgBox "" & Intersect(Target, Range(ColRange))
If Intersect(Target, Range(ColRange)) Is Nothing Then Exit Sub
 
Target.Font.Name = "marlett"
If Target.Value <> "a" Then
   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]

Progress.....See if the changes below help. Also you need to add an error handler to the runmacro(). If you need the code for that, let me know. Let know if the code changes helped any.

Sub RunMacro()
' opens the specified Spreadsheet
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.Worksheet(1).Activate '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Worksheet_BeforeDoubleClick .Range(ActiveCell.Address), False
End With
Exit_Routine: '<<<<<<<<<<<<<<<
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[JFKJr]

Progress.....See if the changes below help. Also you need to add an error handler to the runmacro(). If you need the code for that, let me know. Let know if the code changes helped any.

Sub RunMacro()
' opens the specified Spreadsheet
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.Worksheet(1).Activate '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Worksheet_BeforeDoubleClick .Range(ActiveCell.Address), False
End With
Exit_Routine: '<<<<<<<<<<<<<<<
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

HI, I have changed the code as per your suggestions but it is giving me "Run-time error '438': Object doesn't support this property or method" error on line #10.

Thanks a lot for your help. Hopefully we are coming towards the solution!

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.Worksheet(1).Activate
Worksheet_BeforeDoubleClick .Range(ActiveCell.Address), False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

change this: xlWB.Worksheet(1).Activate

to this: xlWB.Worksheets(1).Activate


or this: Worksheets(1).Activate

[JFKJr]

change this: xlWB.Worksheet(1).Activate

to this: xlWB.Worksheets(1).Activate


or this: Worksheets(1).Activate

Yes, I changed it to "xlWB.Worksheets(1).Activate", now there is no "Run-time error '438': Object doesn't support this property or method" error.

But, it is working same as the code in post #17. I do not see any difference :(

[puppydogbuddy]

I think we need to activate a cell within the worksheet(via code) to trigger the process. I will be out of the office until later today. I will get back to you. If you want play with it, Iook at this reference.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[JFKJr]

I think we need to activate a cell within the worksheet(via code) to trigger the process. I will be out of the office until later today. I will get back to you. If you want play with it, Iook at this reference.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Sure, I will workaround with the code and see how it goes!

Have a great day! :-)

[puppydogbuddy]

ok, let me know what happens with this code:

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
Worksheet_BeforeDoubleClick ActiveCell, False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[JFKJr]

ok, let me know what happens with this code:

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
Worksheet_BeforeDoubleClick ActiveCell, False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

Hi, the above code automatically activated "A5" cell and checked/ticked the cell.

And, when I double-click on other cell it is not working.

[puppydogbuddy]

Can you put code breaks before and after the double click and tell me what values are shown for the target vs the active cell?

[JFKJr]

Can you put code breaks before and after the double click and tell me what values are shown for the target vs the active cell?

ok, I have used both the code breaks to know the program flow and "MsgBox" to know the "Target" and "ActiveCell" values.

This is what happening:

1. For the first time when I run "RunMacro()" proc,
a) The value of "ActiveCell" is empty at line #12 (since there is no value in the "a5" cell) and it called "Worksheet_BeforeDoubleClick" proc.
b) In the called proc the value of "Target" is empty at line #3, the following steps are executed and checked/ticked the "a5" cell.

2. For the second time when I run "RunMacro()" proc,
a)The value of "ActiveCell" is "a" at line #12 (since "a5" cell has "a" value) and it called "Worksheet_BeforeDoubleClick" proc.
b) In the called proc the value of "Target" is "a" at line #3, the following steps are executed and unchecked the "a5" cell.

Hope this helps!

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  
Worksheet_BeforeDoubleClick ActiveCell, False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ColRange As String, LastUsedRow As Long
 MsgBox "" & Target 
If Target.Count > 1 Then Exit Sub
 
If WorksheetFunction.CountA(Cells) > 0 Then
   LastUsedRow = Cells.Find(What:="*", After:=[A1], _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
    'MsgBox "" & LastUsedRow
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
   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]

insert this code between lines 10 and 11 and let me know results:

ActiveCell.Value = "Hello"

[JFKJr]

insert this code between lines 10 and 11 and let me know results:

ActiveCell.Value = "Hello"

ok, I have entered the above code in between 10 and 11 lines,

1. For the first time,
a) "ActiveCell" value is "Hello" at line #12 and "Worksheet_BeforeDoubleClick" proc is called.
b) And in the called proc, "Target" value is "Hello" at line #3, the following steps are executed and checked/ticked the "a5" cell.

2. For the second time,
a) "ActiveCell" value is "Hello" at line #12 and "Worksheet_BeforeDoubleClick" proc is called.
b) And in the called proc, "Target" value is "Hello" at line #3, the following steps are executed and checked/ticked the "a5" 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:\ExtractPolicyList2.xls", , False)
xlWB.Worksheets(1).Activate
ActiveCell.Value = "Hello"
xlWB.ActiveSheet.Range("a5").Select
MsgBox "" & ActiveCell
Worksheet_BeforeDoubleClick ActiveCell, False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

So is working or not? if not, tell me what was supposed happen in terms of the previous post.

[JFKJr]

So is working or not? if not, tell me what was supposed happen in terms of the previous post.

Hi, the previous code is working fine but the code is automatically selecting/checking only the "A5" cell. It is not working when I double click on any other cell.

[puppydogbuddy]

The initial cell must have a value to trigger your function.


Change line 11 to:
If IsEmpty(Range("a5")) Then
Range("a5").Cells(1,5).Value = "Hello"
End If

[JFKJr]

The initial cell must have a value to trigger your function.


Change line 11 to:
If IsEmpty(Range("a5")) Then
Range("a5").Cells(1,5).Value = "Hello"
End If

Hello puppydogbuddy, just like the past, the code is selecting only "A5" cell and is not invoking "Worksheet_BeforeDoubleClick" proc when I double click on any other cell.

I am confused, because the code in the post #1 is working perfectly fine in excel VBA, it is triggering "Worksheet_BeforeDoubleClick" event when I double click on any cell.

Why it is not happening here? Please help me on this!

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
If IsEmpty(Range("a5")) Then
Range("a5").Cells(1, 5).Value = "Hello"
End If
xlWB.ActiveSheet.Range("a5").Select
MsgBox "" & ActiveCell
Worksheet_BeforeDoubleClick ActiveCell, False
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

Hello puppydogbuddy, just like the past, the code is selecting only "A5" cell and is not invoking "Worksheet_BeforeDoubleClick" proc when I double click on any other cell.

I am confused, because the code in the post #1 is working perfectly fine in excel VBA, it is triggering "Worksheet_BeforeDoubleClick" event when I double click on any cell.

Why it is not happening here? Please help me on this!

Apparently, your function does not loop automatically in Access VBA like it does in pure excel??

Change the following code from this:

MsgBox "" & ActiveCell
Worksheet_BeforeDoubleClick ActiveCell, False
End With

To this:

MsgBox "" & ActiveCell
End With
 
Do Until Target.Value = 0
    Worksheet_BeforeDoubleClick ActiveCell, False
Loop

[puppydogbuddy]

PS:
you need to determine the criteria that terminates the loop....I used Target.Value = 0 for illustrative purposes.

[JFKJr]

Apparently, your function does not loop automatically in Access VBA like it does in pure excel??

Change the following code from this:

MsgBox "" & ActiveCell
Worksheet_BeforeDoubleClick ActiveCell, False
End With

To this:

MsgBox "" & ActiveCell
End With
 
Do Until Target.Value = 0
    Worksheet_BeforeDoubleClick ActiveCell, False
Loop

hmm, I replaced the code but it is generating "Run-time error 424: Object required" error at line #14, since there is no "Target" object defined in the proc.

So, I replaced "Target.Value" at line #14 with "ActiveCell.Value" then it is coming out of the loop without calling "Worksheet_BeforeDoubleClick" proc.

Is there any way in Access VBA to run a macro when a cell in an excel file is clicked. Does it is possible to do? I am clueless!

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 = 0
    Worksheet_BeforeDoubleClick ActiveCell, False
Loop
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[JFKJr]

Hello puppudogbuddy, let us make the problem short so that we can only concentrate on the main cause.

I would like to run the following macro whenever a cell in the excel file is clicked, is it possible to do in Access VBA?

Sub RunMacro()
MsgBox "Hello! You have clicked a cell!"
End Sub

I am stuck with this!
Your idea/help will be greatly appreciated. Thanks!

[puppydogbuddy]

try this:

Do Until Target.Value = "a"
Worksheet_BeforeDoubleClick Target = ActiveCell, False
Loop

[JFKJr]

try this:

Do Until Target.Value = "a"
Worksheet_BeforeDoubleClick Target = ActiveCell, False
Loop

Same again, the code is checking only "A5" cell and coming out of the loop.

I think when I clicked on other cell the "RunMacro()" proc is not running, if we make that proc running when the cell is clicked then the code will check/ tick the active cell.

Let us make this simple and run the following simple proc when a cell is clicked as I mentioned in the above post.

What do you think?

Sub RunMacro()
MsgBox "Hello! You have clicked a cell!"
End Sub

[puppydogbuddy]

i will get back to you tomorrow.......one thing you can try is changing your function from Private to Public.....

[puppydogbuddy]

I think we are on the right track it now. We were missing a subroutine called Worksheet_SelectionChange, which is an event handler that will be called automatically by Excel to execute Worksheet_BeforeDoubleClick whenever the user selects a different cell. Also note that the “Target” parameter represents the cell that the user has just selected.....I used instead of ActiveCell because it is the listed parameter and is defined as a range object.

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
Worksheet_SelectionChange Target.Value
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

------------------------------------------------------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Worksheet_BeforeDoubleClick Target.Value
 
End Sub

[JFKJr]

I think we are on the right track it now. We were missing a subroutine called Worksheet_SelectionChange, which is an event handler that will be called automatically by Excel to execute Worksheet_BeforeDoubleClick whenever the user selects a different cell. Also note that the “Target” parameter represents the cell that the user has just selected.....I used instead of ActiveCell because it is the listed parameter and is defined as a range object.

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
Worksheet_SelectionChange Target.Value
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

------------------------------------------------------------------------------------------------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Worksheet_BeforeDoubleClick Target.Value
 
End Sub

Hello puppydogbuddy, the code is automatically selecting only "a5" cell and coming out of macro. It is not calling "Worksheet_SelectionChange" event handler automatically when I select other cells.

Do you think Access VBA supports event handlers or should we have to select any reference to enable them?

Because, if Access VBA supports event handlers then it should have supported "Worksheet_BeforeDoubleClick" event handler also, which is automatically called whenever user double clicks on a cell just like in excel VBA.

I am using MS Access 2003 format.

Please feel free to correct me if I am wrong.

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
Worksheet_SelectionChange ActiveCell
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheet_BeforeDoubleClick Target, False
End Sub

Please note that the following are the changes in the above code:

1. changed "Worksheet_SelectionChange Target.Value" to "Worksheet_SelectionChange ActiveCell" at line #13. Because it is generating "Object required" error.

2. changed "Worksheet_BeforeDoubleClick Target.Value" to "Worksheet_BeforeDoubleClick Target, False" at line #2, since the proc takes two arguments.

Please let me know if the above changes affect the solution. Thanks!

[puppydogbuddy]

You should have a reference set to the excel object library.

it should be Target because target is rangebject and ActiveCell is not.

I just found a vba tutorial that demos code very close to what we are doing above. Read it very carefully and see if you can spot anything we did wrong.

http://pubs.logicalexpressions.com/P...cle.asp?ID=407

[JFKJr]

You should have a reference set to the excel object library.

it should be Target because target is rangebject and ActiveCell is not.

I just found a vba tutorial that demos code very close to what we are doing above. Read it very carefully and see if you can spot anything we did wrong.

http://pubs.logicalexpressions.com/P...cle.asp?ID=407

Hello puppydogbuddy, I am sorry that I did not exactly follow the above post, do you mean that we need to set a reference to excel object library, if yes then which library do we need to set?
And, I already had a reference to "Microsoft Excel 11.0 Object Library".

ok, as per your suggestion I changed "Worksheet_SelectionChange ActiveCell" to "Worksheet_SelectionChange Target.Value" at line #13. But, it is generating "Run-time error '424': Object required" error. What should I do?

And regarding the article, it is mentioning only about the event handlers using excel VBA. The event handlers such as "Worksheet_SelectionChange" and "Worksheet_BeforeDoubleClick" working perfectly fine in excel VBA.

But my question is does Access VBA support excel event handlers?

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
Worksheet_SelectionChange Target.Value
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

Hello puppydogbuddy, I am sorry that I did not exactly follow the above post, do you mean that we need to set a reference to excel object library, if yes then which library do we need to set?
And, I already had a reference to "Microsoft Excel 11.0 Object Library".

ok, as per your suggestion I changed "Worksheet_SelectionChange ActiveCell" to "Worksheet_SelectionChange Target.Value" at line #13. But, it is generating "Run-time error '424': Object required" error. What should I do?

And regarding the article, it is mentioning only about the event handlers using excel VBA. The event handlers such as "Worksheet_SelectionChange" and "Worksheet_BeforeDoubleClick" working perfectly fine in excel VBA.

But my question is does Access VBA support excel event handlers?

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
Worksheet_SelectionChange Target.Value
End With
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

you have the correct reference

change : Worksheet_SelectionChange Target.Value
to: Worksheet_SelectionChange Target

Access VBA supports excel event handlers once excel automation object is created, which has been done at the begining of your code.

[JFKJr]

you have the correct reference

change : Worksheet_SelectionChange Target.Value
to: Worksheet_SelectionChange Target

Access VBA supports excel event handlers once excel automation object is created, which has been done at the begining of your code.

Yes, I even changed it to "Worksheet_SelectionChange Target" and it is giving me same "Object Required" error.

And, it's a relief to know that Access VBA supports excel event handlers. But I have searched internet a lot and unfortunately I didn't found any article on using "Worksheet_SelectionChange" and "Worksheet_BeforeDoubleClick" event handlers in Access VBA!

[puppydogbuddy]

on the obj error, try moving that code line to the line after the "end with" statement.

the beauty of vba is that once an instance of excel automation object is created, Access has access to most of excels objects, methods, etc.

idea: try copying the code from the vba tutorial and see if it runs

[JFKJr]

on the obj error, try moving that code line to the line after the "end with" statement.

the beauty of vba is that once an instance of excel automation object is created, Access has access to most of excels objects, methods, etc.

idea: try copying the code from the vba tutorial and see if it runs

Hi, I moved the code to the line after the "end with" statement and it is giving same "Object Required" error.

And, regarding the vba tutorial I used the following code in both excel and Access:

1. In excel, every time I switch to the worksheet, the event is called and it is changing the rectangular cells to square
2. But in access, the event is called only for the first time I run "RunMacro()" proc below.

The problem is, the code is running properly in both excel and access. But, in access the events doesn't trigger unless we manually run a proc, which calls the event.

Private Sub Worksheet_Activate()
Columns.ColumnWidth = 1
Rows.RowHeight = 8
End Sub

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
'Worksheet_SelectionChange Target
Worksheet_Activate
Exit_Routine:
Set xlApp = Nothing
Set xlWB = Nothing
Exit Sub
End Sub

[puppydogbuddy]

Hi, I moved the code to the line after the "end with" statement and it is giving same "Object Required" error.

And, regarding the vba tutorial I used the following code in both excel and Access:

1. In excel, every time I switch to the worksheet, the event is called and it is changing the rectangular cells to square
2. But in access, the event is called only for the first time I run "RunMacro()" proc below.

The problem is, the code is running properly in both excel and access. But, in access the events doesn't trigger unless we manually run a proc, which calls the event.

Am with a client and will be busy for about 2 hours.

I am not sure what you mean in the following statements?
The problem is, the code is running properly in both excel and access. But, in access the events doesn't trigger unless we manually run a proc, which calls the event

1.which event doesn't trigger ? the SelectionChange event?

2. in access the events doesn't trigger unless we manually run a proc, which calls the event.....run which proc ....the RunMacro?

[JFKJr]

Am with a client and will be busy for about 2 hours.

I am not sure what you mean in the following statements?
The problem is, the code is running properly in both excel and access. But, in access the events doesn't trigger unless we manually run a proc, which calls the event

1.which event doesn't trigger ? the SelectionChange event?

2. in access the events doesn't trigger unless we manually run a proc, which calls the event.....run which proc ....the RunMacro?

Sorry to confuse you, well this is what I mean:

for example lets take "Worksheet_Activate" event handler from the above post,

1. In excel, this event is called every time I switch to the worksheet.
2. But, in access the event is called only when I run above "RunMacro()" proc, which calls "Worksheet_Activate" event.

In contrary to excel VBA, the access vba is not calling the event whenever the user switches to the current worksheet.