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|Reference s
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|Reference s
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>g eneral 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>g eneral 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_Befo reDoubleClick" 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_Befo reDoubleClick" 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.Add ress

instead of your a3 parameter

Let me know what happens.

[JFKJr]

Ok,
That was an excellent explanation.

Try this:
Target.Value = Active.Cell.Add ress

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_Befo reDoubleClick" function.

[puppydogbuddy]

My fault.

Change this:
.Range(Target.V alue = Active.Cell.Add ress)

to this:
Change this:
.Range(Target.V alue = ActiveCell.Addr ess)

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.Addr ess.

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.V alue = Active.Cell.Add ress)

to this:
Change this:
.Range(Target.V alue = ActiveCell.Addr ess)

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.Addr ess.

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.Val ue = ActiveCell.Addr ess" 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_Befo reDoubleClick" 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.activeCel l
with xlApp
.worksheets(1). cells(1,1) = somevalue
.activesheet.se lect
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.ActiveSh eet.worksheet_b eforedoubleclic k perhaps?)

-Stewart

[puppydogbuddy]

Hi Stewart,
Thanks for that info. It is definitely part of the problem...unfor tunately 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_befor eDoubleClick 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_befor eDoubleClick 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_Sele ctionChange" 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.