I have a worksheet that has 12 different sections (one for each month) used
to enter data to balance a checkbook. I use a commandbutton for each month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR, etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton was
clicked so that I do not have to edit each of the 12 macros and change the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and then do
a RIGHT function and get the number. In the properties of each commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

If they are Forms buttons

Private Sub Button1_Click()
Dim mnth As String
mnth = ActiveSheet.Buttons(Application.Caller).Caption & "08"
Application.Goto Reference:=mnth
End Sub

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
I have a worksheet that has 12 different sections (one for each month) used
to enter data to balance a checkbook. I use a commandbutton for each
month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR, etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton was
clicked so that I do not have to edit each of the 12 macros and change the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and then
do
a RIGHT function and get the number. In the properties of each
commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

What do you mean by Forms buttons? I am just using commandbuttons1 -
commandbutton12.

Les

"Bob Phillips" wrote:

If they are Forms buttons

Private Sub Button1_Click()
Dim mnth As String
mnth = ActiveSheet.Buttons(Application.Caller).Caption & "08"
Application.Goto Reference:=mnth
End Sub

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
I have a worksheet that has 12 different sections (one for each month) used
to enter data to balance a checkbook. I use a commandbutton for each
month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR, etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton was
clicked so that I do not have to edit each of the 12 macros and change the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and then
do
a RIGHT function and get the number. In the properties of each
commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

What commandbuttons 1-12? On a userform? From the control toolbox? or
something else?

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
What do you mean by Forms buttons? I am just using commandbuttons1 -
commandbutton12.

Les

"Bob Phillips" wrote:

If they are Forms buttons

Private Sub Button1_Click()
Dim mnth As String
mnth = ActiveSheet.Buttons(Application.Caller).Caption & "08"
Application.Goto Reference:=mnth
End Sub

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
I have a worksheet that has 12 different sections (one for each month)
used
to enter data to balance a checkbook. I use a commandbutton for each
month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then
I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR,
etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton
was
clicked so that I do not have to edit each of the 12 macros and change
the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and
then
do
a RIGHT function and get the number. In the properties of each
commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

From the control toolbox. I am not using a userform.

Les


"Bob Phillips" wrote:

What commandbuttons 1-12? On a userform? From the control toolbox? or
something else?

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
What do you mean by Forms buttons? I am just using commandbuttons1 -
commandbutton12.

Les

"Bob Phillips" wrote:

If they are Forms buttons

Private Sub Button1_Click()
Dim mnth As String
mnth = ActiveSheet.Buttons(Application.Caller).Caption & "08"
Application.Goto Reference:=mnth
End Sub

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
I have a worksheet that has 12 different sections (one for each month)
used
to enter data to balance a checkbook. I use a commandbutton for each
month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then
I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR,
etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton
was
clicked so that I do not have to edit each of the 12 macros and change
the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and
then
do
a RIGHT function and get the number. In the properties of each
commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

Create a class module named clsActiveXEvents with the following code

Option Explicit

Public WithEvents mButtonGroup As MSForms.CommandButton

Private Sub mButtonGroup_Click()
Dim mnth As String
mnth = mButtonGroup.Caption & "08"
Application.Goto Reference:=mnth
End Sub


and in the worksheet module, add

Option Explicit

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cBtnEvents As clsActiveXEvents
Dim shp As Shape

Set mcolEvents = New Collection

For Each shp In Me.Shapes
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.CommandButton
Then
Set cBtnEvents = New clsActiveXEvents
Set cBtnEvents.mButtonGroup = shp.OLEFormat.Object.Object
mcolEvents.Add cBtnEvents
End If
End If
Next

End Sub


--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
From the control toolbox. I am not using a userform.

Les


"Bob Phillips" wrote:

What commandbuttons 1-12? On a userform? From the control toolbox? or
something else?

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
What do you mean by Forms buttons? I am just using commandbuttons1 -
commandbutton12.

Les

"Bob Phillips" wrote:

If they are Forms buttons

Private Sub Button1_Click()
Dim mnth As String
mnth = ActiveSheet.Buttons(Application.Caller).Caption & "08"
Application.Goto Reference:=mnth
End Sub

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
I have a worksheet that has 12 different sections (one for each
month)
used
to enter data to balance a checkbook. I use a commandbutton for
each
month.
I am not quite sure how to explain what I want to do so I will use
the
"normal" code and the the code I have to replace the normal code.
Then
I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR,
etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which
commandbutton
was
clicked so that I do not have to edit each of the 12 macros and
change
the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and
then
do
a RIGHT function and get the number. In the properties of each
commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

look at Application.Caller
Put a Msgbox Application.Caller in the code of the button so you can see
what it returns to you - you can probably take advantage of that in one
subroutine.

"WLMPilot" wrote:

I have a worksheet that has 12 different sections (one for each month) used
to enter data to balance a checkbook. I use a commandbutton for each month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR, etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton was
clicked so that I do not have to edit each of the 12 macros and change the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and then do
a RIGHT function and get the number. In the properties of each commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les

I typed in "MsgBox Application.Caller" (without quotes) and got a Type Mismatch

Les

"Bob Umlas, Excel MVP" wrote:

look at Application.Caller
Put a Msgbox Application.Caller in the code of the button so you can see
what it returns to you - you can probably take advantage of that in one
subroutine.

"WLMPilot" wrote:

I have a worksheet that has 12 different sections (one for each month) used
to enter data to balance a checkbook. I use a commandbutton for each month.
I am not quite sure how to explain what I want to do so I will use the
"normal" code and the the code I have to replace the normal code. Then I
will ask the question.

NORMAL CODE

Private Sub CommandButton1_Click()
Application.Goto Reference:="JAN08"
End Sub

NEW CODE TO DO THE SAME THING

Private Sub CommandButton1_Click()
Dim mnth As String
mnth = CommandButton1.Caption & "08"
Application.Goto Reference:=mnth
End Sub

The caption is the first three letters of each month (JAN, FEB, MAR, etc.)
The cell name for each month is JAN08, FEB08, etc.

Here is my question. Is there a way to determine which commandbutton was
clicked so that I do not have to edit each of the 12 macros and change the
numerical value of each commandbutton within the subroutine?

I was trying to pull out the "name", just as I did with caption, and then do
a RIGHT function and get the number. In the properties of each commandbutton
is "(NAME)". I was not sure if I could access that field.

Thanks,
Les