Passing argument error

[romtoss]

Hello -

I'm new to the forum but not new to Excel VBA. I'm having a problem passing an argument. I've written a number of Excel macros, but they've been simple and done within a single module or didn't require passing of arguments. This seems very simple, yet I'm having trouble with it. Can anyone assist and let me know what I'm doing wrong please? Thank you!

Option Explicit

Public wkshtSelected As String   ' declaring my variable


Private Sub cboSheetNames_Change()

    wkshtSelected = frmUserPrompt.cboSheetNames.Value   ' setting variable to value user selects from combo box on form (values are all sheets in this workbook)
    Sheets(wkshtSelected).Select   ' select that worksheet

End Sub


Private Sub cmdLoadExpenses_Click(ByRef wkshtSelected As Worksheet)   ' now that wkshtSelected has value, I'm trying to pass it to this sub for further code but get following error

     'Compile error: Procedure declaration does not match description of event or procedure having the same name.


     'other code will go here once argument passing error is resolved

End Sub

 

[JLGWhiz]

Your public declaration for skshtSelected is As String and is used properly in cboSheetNames_Change, but then you attempt to change the data type from string to a worksheet object in cmdLoadExpenses_Click. That won't work because you have not initialized the variable as a worksheet, only as a string. You would need to use it in the same fashion as in the first macro and change your declaration in the second macro to comply with the public declaration.

 

[Kenneth Hobson]

Welcome to the forum!

You declared it as String in 2nd line of code and as Worksheet in the Sub. When you Compile or Run, Excel says what do you mean, that is not right.

If it is selected, why not use ActiveSheet in your Sub? I find that Select and Activate are seldom needed.

 

[romtoss]

I actually am trying to pass it to the cmdLoadExpenses_Click sub as a String. I know my original post's code showed otherwise, but that was a typo. Sorry, been changing code around and had that messed up.

I have fixed it so now it references String in all places but still get the exact same error. All other code is unchanged, just replaced 'worksheet' with 'string'

 

[JLGWhiz]

Follow-up to post #3: Since you have declared the variable as a Public variable, you do not have to declare it again in the macros. But you do need to initialize it, as you have done in the first macro. You do not have to re-initialize it in the second macro.

 

[RoryA]

Assuming that cmdLoadExpenses is a button, you cannot change its Click event to take an argument (it wouldn't really make any sense).

 

[JLGWhiz]

I have fixed it so now it references String in all places but still get the exact same error. All other code is unchanged, just replaced 'worksheet' with 'string'

Just use this for your title line of the second macro.

Private Sub cmdLoadExpenses_Click()

and then use the variable in the code as Sheets(wkshtSelected) and it should work OK.

 

[Kenneth Hobson]

I guess this is in a Userform? If so, it might go something like:

Private wkshtSelected As Worksheet   ' declaring my variable

Private Sub UserForm_Initialize()
  Dim i%
  For i = 1 To Worksheets.Count
    cboSheetNames.AddItem Worksheets(i).Name
  Next i
End Sub

Private Sub cboSheetNames_Change()
    Set wkshtSelected = Worksheets(cboSheetNames.Value)   ' setting variable to value user selects from combo box on form (values are all sheets in this workbook)
    wkshtSelected.Select   ' select that worksheet
End Sub

Private Sub cmdLoadExpenses_Click()
  MsgBox ActiveSheet.Name
  On Error Resume Next
  MsgBox wkshtSelected.Name
End Sub