passing data between procedures

[Matthew Leggett]

I am trying to get a variable to pass to another procedure. I have set my procedure as follows Sub Procedure(X) ' programming code End Sub Normally I would use the following to send data Procedure1 Count6 however in this case I am running the called procedure using this code With ActiveWorkbook.Sheets(SetMonth).Buttons.Add(600, 20, 100, 25) .OnAction = "Procedure1" .Caption = "Bills Not Paid July" .Name = "JULY" End With how do I adapt the ".OnAction = "Procedure1" section to pass the variable" Can anyone help me

[ChandraRam]

Hi The issue with specifying the parameter when you create the button is that the value for the parameter will be fixed. For instance:

Public Sub CreateButton()
With ActiveWorkbook.Sheets("sheet1").Buttons.Add(600, 20, 100, 25)
.OnAction = "'Procedure1 ""Hello"", " & nCount & "'"
.Caption = "Bills Not Paid July"
.Name = "JULY"
End With
End Sub

Public Sub Procedure1(ByVal s As String, ByVal x As Integer)
MsgBox s & " " & x
End Sub

In the code above, even though the parameter is specified as a variable, the value when you _define_ the OnAction is what will be passed in. To get around this, you could try this:

Public Sub CreateButton()
With ActiveWorkbook.Sheets("sheet1").Buttons.Add(600, 20, 100, 25)
.OnAction = "July_Click"
.Caption = "Bills Not Paid July"
.Name = "JULY"
End With
End Sub

Public Sub July_Click()
Call ActiveWorkbook.Application.Run("Procedure1", "test", nCount)
End Sub

Public Sub Procedure1(ByVal s As String, ByVal x As Integer)
MsgBox s & " " & x
End Sub

HTH