G'day there People once again,

I'm currently working on a spreadsheet to enter a monthly shift
roster. I have a userform with, amongst other things, 2 columns of
Command Buttons with 24hrs of shifts as captions: 6a - 2p, 7a - 3p, etc.
The On_Click() event of each command button enters the button's caption
into a variable ("sHift") which is displayed in the userform's textbox
control which may be amended before the 'Enter' button copies it to the
ActiveCell.

As this spreadsheet will possibly be adopted throughout the entire
department (about 9,000 employees), this arrangement will suit most but
not all situations. I have therefore added 6 blank command buttons for
user defined shifts. Also, there are some areas that won't need all the
shifts I've defined.

My approach to this is to allow the user to enter text into the
'sHift' textbox and press the "Define Shift" button. All the buttons
with shifts, and the blanks, then change to a Magenta background with
white & bold text (very pretty I might add). My intention is for the
user to then click one of the 30 'shift' buttons to assign their entered
text to the chosen button's caption, thus becoming a new shift.

However, I've already assigned each button's On_Click() event to
enter the caption for the sHift. Is there a way I can tell which button
has been pressed without the individual On_Click() event? Is there a way
to scan the 30 buttons and determine which has been pressed? If I can
get that then I can simply reassign the button's caption and the problem
is solved. (BTW, there are more than 30 command buttons, but all the
others have had their names changed. All the 'Shift' buttons still have
the default names e.g. CommandButton1, CommandButton23, etc. With one or
two exceptions (which can be changed) the names are contiguous so it's
not too hard to iterate through the list.

It's not an insurmountable problem as I can also enter code into
each button's On_Click() event to check the background colour and take
separate actions as desired. However with 30 buttons, that's a pain in
the rear and I'd prefer to learn how to implement the described
technique if it's at all possible.

Thanks for listening.
Ken McLennan
Qld, Australia.

Ken

http://j-walk.com/ss/excel/tips/tip44.htm

describes how to use one procedure to control multiple userform controls.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Ken McLennan" wrote in message
.. .
G'day there People once again,

I'm currently working on a spreadsheet to enter a monthly shift
roster. I have a userform with, amongst other things, 2 columns of
Command Buttons with 24hrs of shifts as captions: 6a - 2p, 7a - 3p, etc.
The On_Click() event of each command button enters the button's caption
into a variable ("sHift") which is displayed in the userform's textbox
control which may be amended before the 'Enter' button copies it to the
ActiveCell.

As this spreadsheet will possibly be adopted throughout the entire
department (about 9,000 employees), this arrangement will suit most but
not all situations. I have therefore added 6 blank command buttons for
user defined shifts. Also, there are some areas that won't need all the
shifts I've defined.

My approach to this is to allow the user to enter text into the
'sHift' textbox and press the "Define Shift" button. All the buttons
with shifts, and the blanks, then change to a Magenta background with
white & bold text (very pretty I might add). My intention is for the
user to then click one of the 30 'shift' buttons to assign their entered
text to the chosen button's caption, thus becoming a new shift.

However, I've already assigned each button's On_Click() event to
enter the caption for the sHift. Is there a way I can tell which button
has been pressed without the individual On_Click() event? Is there a way
to scan the 30 buttons and determine which has been pressed? If I can
get that then I can simply reassign the button's caption and the problem
is solved. (BTW, there are more than 30 command buttons, but all the
others have had their names changed. All the 'Shift' buttons still have
the default names e.g. CommandButton1, CommandButton23, etc. With one or
two exceptions (which can be changed) the names are contiguous so it's
not too hard to iterate through the list.

It's not an insurmountable problem as I can also enter code into
each button's On_Click() event to check the background colour and take
separate actions as desired. However with 30 buttons, that's a pain in
the rear and I'd prefer to learn how to implement the described
technique if it's at all possible.

Thanks for listening.
Ken McLennan
Qld, Australia.

G'day there Dick,

http://j-walk.com/ss/excel/tips/tip44.htm

describes how to use one procedure to control multiple userform controls.

Thanks for that. I've had a look and it seemed quite interesting.
I'm just checking out the example now.

Thanks again,
Ken McLennan
Qld, Australia