help useing DoubleClick

[IvanK]

I have written a program that uses Excel VBA to allow clients to sign in and out at the day care where I take my wife. I initially set up the program to ask the client to type in his client number, but now I want to have the client DoubleClick on his name in a list to get his sheet number. I cannot get the double click to work.
The Work Book opens at Sheet1 which is named Clients and has the client’s name in col A and the client’s sheet number in col B. I want the client to double click his name and have the code activate his sign in/out sheet. I can not get the double click to work. Can someone help me.
Attached is the code from the beginning of the program.

Option Compare Text

Const BLANK = ""

'Private Sub Workbook_Open()
'Application.ExecuteExcel4Macro "show.toolbar(""Ribbon"",True)"
'Call GetClientNum
'End Sub
'Sub GetClientNum()

'End Sub
'MsgBox "get client name"
Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    Dim wks As Worksheet
 
    If target.Column = 1 And _
       target.Row >= 2 And _
       target.Value <> "" Then

        '  The check above is used to ensure we are looking at a client cell.

        Set wks = Worksheets(target.Offset(, 1).Value) 'this addresses the client sheet name

        '  Set CANCEL to TRUE so Excel will not try to handle the double-click event.
        Cancel = True

        'Set wks = Worksheets(target.Offset(, 1).Value)
        wks.Activate
Call xit
End Sub

 

[DanteAmor]

Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sName As String
  If Target.Column = 1 And Target.Row >= 2 And Target.Value <> "" Then
    Cancel = True
    sName = Target.Offset(, 1).Value
    If sName = "" Then
      MsgBox "There is no reference sheet."
    Else
      If Evaluate("ISREF('" & sName & "'!A1)") Then
        Sheets(sName).Select
        Call xit
      Else
        MsgBox "The sheet does not exist"
      End If
    End If
  End If
End Sub

 

[IvanK]

Thanks for replying. My problem is that the double click on the client list page dose not seem to run the code. If I put a MsgBox right after "Dim wks As Workesheet" I don't get the message box. Is there something needed to link the Client sheet to the code sheet? I can send a copy of the Client sheet or my complete program if that will help.
Ivan

 

[DanteAmor]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook

 

[IvanK]

Thanks, I have to go to get my wife from day care and I will try this latter.

 

[DanteAmor]

Of course, let me know any questions.

 

[IvanK]

Thank you Dante, That is what I was missing. I have two more questions. Can the statement "If Target.Column = 1 " be changed to 1 or 4 or 7, so I would be able to put the list in three columns to keep it on one page. Also can you explain the statement "If Evaluate("ISREF('" & sName & "'!A1)")".

 

[DanteAmor]

Thank you Dante, That is what I was missing. I have two more questions. Can the statement "If Target.Column = 1 " be changed to 1 or 4 or 7, so I would be able to put the list in three columns to keep it on one page.
Check if the reference to sheet and cell A1 exists, if it returns false, then the sheet does not exist.
Also can you explain the statement "If Evaluate("ISREF('" & sName & "'!A1)")".

Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim sName As String
  Select Case Target.Column
    Case 1, 4, 7
      If Target.Row >= 2 And Target.Value <> "" Then
        Cancel = True
        sName = Target.Offset(, 1).Value
        If sName = "" Then
          MsgBox "There is no reference sheet."
        Else
          If Evaluate("ISREF('" & sName & "'!A1)") Then
            Sheets(sName).Select
            Call xit
          Else
            MsgBox "The sheet does not exist"
          End If
        End If
      End If
  End Select
End Sub

 

[IvanK]

Thanks this is a real help. I am 84 years old and my brain does not work very well any more. I have spent a lot of time on the internet to get this far on the project.

 

[DanteAmor]

Glad to help you, thanks for the feedback.