Command Button question
 

Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command Buttons.

Part 1: The 40+ lines of code in each button is identical, except for
some range references. ( eg cmdA, cmdB ...etc refer to Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter the code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code to be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle

Command Button question
 

Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new commandbar
with 5 controls (each a commandbutton) - that way it'd always be visible, no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command Buttons.

Part 1: The 40+ lines of code in each button is identical, except for
some range references. ( eg cmdA, cmdB ...etc refer to Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle

Command Button question
 

Hello Giselle,

Rather than give you a general description of what to do and talking
back forth about changes, It would be faster to see the command button
as it is. Can you post the code?

Thank you,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=505823

Command Button question
 

Just a few followup clarifications:

The case I gave in the original post was slightly simplified. There are
actually 3 distinct ranges needed each time the button is clicked.
Something like:

Sub Giselle()
Select Case Application.Caller
Case "Button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Common
Case "Button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "UV"
Common
etc..

So... would the common sub look something like this?

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
..... etc.

Thanks! I'm really new at this. I'm assuming all of the above goes in a
Module. Giselle






"Bob Umlas" wrote in message
...
Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new commandbar
with 5 controls (each a commandbutton) - that way it'd always be visible,
no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command Buttons.

Part 1: The 40+ lines of code in each button is identical, except for
some range references. ( eg cmdA, cmdB ...etc refer to
Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does
the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle

Command Button question
 

Just to add to Bob's reply. You'll want to use buttons from the Forms
toolbar--not commandbuttons from the Control toolbox.

And then:

Option Explicit
Sub Giselle()
Dim R1 as String
dim R2 as string
dim R3 as string

R1 = ""
R2 = ""
R3 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "U:V" '<--changed
End Select

if r1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
end if

call Common(r1,r2,r3)

End Sub

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.....

End Sub

I think I'd be a little specific with what worksheet, too:

Sub Common(R1 as String, R2 as String,R3 as String)

with worksheets("Sheet99") 'or Activesheet ????
Set FirstRangeToWorkOn = .Range(R1)
Set StringToWorkOn = .Range(R2)
Set SecondRangeToWorkOn = .Range(R3)
end with

.....

End Sub

And all of it goes into a general module--remember use the button from the Forms
toolbar.

(if you select the button, you can type the name you like in the
namebox--remember to hit enter when you're done changing it.)

Giselle wrote:

Just a few followup clarifications:

The case I gave in the original post was slightly simplified. There are
actually 3 distinct ranges needed each time the button is clicked.
Something like:

Sub Giselle()
Select Case Application.Caller
Case "Button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Common
Case "Button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "UV"
Common
etc..

So... would the common sub look something like this?

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.... etc.

Thanks! I'm really new at this. I'm assuming all of the above goes in a
Module. Giselle

"Bob Umlas" wrote in message
...
Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new commandbar
with 5 controls (each a commandbutton) - that way it'd always be visible,
no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command Buttons.

Part 1: The 40+ lines of code in each button is identical, except for
some range references. ( eg cmdA, cmdB ...etc refer to
Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does
the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle





--

Dave Peterson

Command Button question
 

I won't get a chance to code this for a few days, but the help offered is
WONDERFUL. It really makes sense.
Giselle

Command Button question
 

Greetings Dave, Bob and other gurus

Dave - I'm new at all this, but I just about completely understand your
comments. Could I ask a few follow ups?

a) I'm unfamiliar with how Application.Caller works. Would SubGiselle() be
in the button's click_event?? If not, what code is there?

b) real newbie question: Sub Giselle() and Sub Common( ... ) must be
accessible by MANY buttons. Where are these Subs to be located? (in a
module?, a class module?, ...)

c) in Sub Giselle(), why did you need to use lcase with the
Application.Caller?

I can't wait to try out this code!
Giselle



"Dave Peterson" wrote in message
...
Just to add to Bob's reply. You'll want to use buttons from the Forms
toolbar--not commandbuttons from the Control toolbox.

And then:

Option Explicit
Sub Giselle()
Dim R1 as String
dim R2 as string
dim R3 as string

R1 = ""
R2 = ""
R3 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "U:V" '<--changed
End Select

if r1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
end if

call Common(r1,r2,r3)

End Sub

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
....

End Sub

I think I'd be a little specific with what worksheet, too:

Sub Common(R1 as String, R2 as String,R3 as String)

with worksheets("Sheet99") 'or Activesheet ????
Set FirstRangeToWorkOn = .Range(R1)
Set StringToWorkOn = .Range(R2)
Set SecondRangeToWorkOn = .Range(R3)
end with

....

End Sub

And all of it goes into a general module--remember use the button from the
Forms
toolbar.

(if you select the button, you can type the name you like in the
namebox--remember to hit enter when you're done changing it.)

Giselle wrote:

Just a few followup clarifications:

The case I gave in the original post was slightly simplified. There are
actually 3 distinct ranges needed each time the button is clicked.
Something like:

Sub Giselle()
Select Case Application.Caller
Case "Button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Common
Case "Button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "UV"
Common
etc..

So... would the common sub look something like this?

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.... etc.

Thanks! I'm really new at this. I'm assuming all of the above goes in a
Module. Giselle

"Bob Umlas" wrote in message
...
Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new
commandbar
with 5 controls (each a commandbutton) - that way it'd always be
visible,
no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command
Buttons.

Part 1: The 40+ lines of code in each button is identical, except
for
some range references. ( eg cmdA, cmdB ...etc refer to
Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter
the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does
the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code
to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle





--

Dave Peterson

Command Button question
 

a) You used the commandbutton from the Control toolbox. Delete those and
replace them with the buttons from the Forms toolbar. Then rightclick on each
and assign the macro Giselle to each of them.

b) Application.caller will return the name of the object (in your case the
button) that was clicked. It works nicely with lots of shapes from the Drawing
toolbar and objects from the Forms toolbar--but won't work with the controls
from the Control toolbox toolbar.

Yes. Put all the code (both subroutines) in a General module--not behind a
worksheet, not behind ThisWorkbook and not in a Class module.

c) If you're like me, you may type a new name for one of the buttons. If
you're not careful, you could end up with "Button 1" or "BUTTON 1" or "BuTtOn 1"
(difficult to believe the last one is an accident!).

But by comparing the lower case name with "button 1", it'll match all -- no
matter how you typed it.

But be careful...You don't want to do this:

Select Case lcase(Application.Caller)
Case "Button 1"

Since there is no lower case string that would match any string with an upper
case letter.



Giselle wrote:

Greetings Dave, Bob and other gurus

Dave - I'm new at all this, but I just about completely understand your
comments. Could I ask a few follow ups?

a) I'm unfamiliar with how Application.Caller works. Would SubGiselle() be
in the button's click_event?? If not, what code is there?

b) real newbie question: Sub Giselle() and Sub Common( ... ) must be
accessible by MANY buttons. Where are these Subs to be located? (in a
module?, a class module?, ...)

c) in Sub Giselle(), why did you need to use lcase with the
Application.Caller?

I can't wait to try out this code!
Giselle

"Dave Peterson" wrote in message
...
Just to add to Bob's reply. You'll want to use buttons from the Forms
toolbar--not commandbuttons from the Control toolbox.

And then:

Option Explicit
Sub Giselle()
Dim R1 as String
dim R2 as string
dim R3 as string

R1 = ""
R2 = ""
R3 = ""

Select Case lcase(Application.Caller)
Case "button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Case "button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "U:V" '<--changed
End Select

if r1 = "" then
beep
msgbox "Design error. See Giselle!
exit sub
end if

call Common(r1,r2,r3)

End Sub

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
....

End Sub

I think I'd be a little specific with what worksheet, too:

Sub Common(R1 as String, R2 as String,R3 as String)

with worksheets("Sheet99") 'or Activesheet ????
Set FirstRangeToWorkOn = .Range(R1)
Set StringToWorkOn = .Range(R2)
Set SecondRangeToWorkOn = .Range(R3)
end with

....

End Sub

And all of it goes into a general module--remember use the button from the
Forms
toolbar.

(if you select the button, you can type the name you like in the
namebox--remember to hit enter when you're done changing it.)

Giselle wrote:

Just a few followup clarifications:

The case I gave in the original post was slightly simplified. There are
actually 3 distinct ranges needed each time the button is clicked.
Something like:

Sub Giselle()
Select Case Application.Caller
Case "Button 1"
R1 = "D5:J25"
R2 = "Ford"
R3 = "O:P"
Common
Case "Button 2"
R1 = "J5:K25"
R2 = "Chrysler"
R3 = "UV"
Common
etc..

So... would the common sub look something like this?

Sub Common(R1 as String, R2 as String,R3 as String)
Set FirstRangeToWorkOn = Range(R1)
Set StringToWorkOn = Range(R2)
Set SecondRangeToWorkOn = Range(R3)
.... etc.

Thanks! I'm really new at this. I'm assuming all of the above goes in a
Module. Giselle

"Bob Umlas" wrote in message
...
Have each button call the exact same routine, say "Giselle"
Each button has a name, like Button 1, Button 2, etc. You can see which
button was clicked by Application.Caller:
Sub Giselle()
Select Case Application.Caller
Case "Button 1"
TheRange = "A1:A5"
Common
Case "Button 2"
TheRange = "G4:G88"
Common
Case "Button 3"
TheRange = "N3:Z3"
Common
'....etc
End Select
End Giselle
Sub Common(Rg as String)
Set RangeToWorkOn = Range(Rg)
'...now work with RangeToWorkOn
End Sub

instead of putting 5 buttons on each sheet, why not use a new
commandbar
with 5 controls (each a commandbutton) - that way it'd always be
visible,
no
matter which sheet you're on.

Bob Umlas
Excel MVP
"Giselle" wrote in message
...
Greetings

I'm fairly new at XL programming and I need a bit of help.

I have a worksheet that contains, among other things, 5 command
Buttons.

Part 1: The 40+ lines of code in each button is identical, except
for
some range references. ( eg cmdA, cmdB ...etc refer to
Range(D5:E25),
Range(J5:K25) ...etc). Can someone suggest how I can only enter
the
code
once to save space and have each button run the code with its own
appropriate ranges.

Part 2: There are eventually going to be 24 worksheets like the one
above, each having these same 5 buttons. On each worksheet, cmdA does
the
exactly same thing. (same for cmdB, etc). Ideally, I'd like the code
to
be
usable by ALL buttons (24x5=120) in the workbook. I'm not sure how to
proceed.

Thank you very much for any help
Giselle





--

Dave Peterson

--

Dave Peterson