Passing Values From Command Buttons

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I pass a value from a command button to a procedure

Let's say I have four command buttons, one labeled "Red", "Green", "Brown" and one labeled "Yellow"
Let's also say that I have a procedure named "ColorCell"

How do I get the procedure to color the cell appropriately depending on which button is clicked

Thank you
Scott
 
Scott,

You can only do it with control commandbuttons, and you can do it from their
Click event procedure, setting the BackColor property to an RGB colour, not
ColorIndex.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Scott

You can use this macro with Forms buttons

It use the name of the button to check out what color it must use

Sub ColorCell()
Dim butname
butname = ActiveSheet.Shapes(Application.Caller).Name
Select Case butname
Case Is = "Red": ActiveCell.Interior.Color = vbRed
Case Is = "Green": ActiveCell.Interior.Color = vbGreen
End Select
End Sub
 
Normally you'd assign a sub (macro) to a button from the
Form menu by right clicking then Assign Macro

To call a procedure passing a variable, in VBA for example

ShowMyColor "RED"

This would call the procedure called "ShowMyColor" ,
passing a text string "RED" to it


This is the same as what you'd type in the assign macro
but you "wrap" the command line in single quotes
eg
'ShowMyColor "RED"'

and

Book1!'Button2_Click "BLUE"'


Patrick Molloy
Microsoft Excel MVP



-----Original Message-----
How do I pass a value from a command button to a procedure?

Let's say I have four command buttons, one
Click to expand...
labeled "Red", "Green", "Brown" and one labeled "Yellow".
Let's also say that I have a procedure named "ColorCell".

How do I get the procedure to color the cell
Click to expand...
appropriately depending on which button is clicked?
 
Dear All

Thanks for your input. I still can't get it to work. What do I need to do to the code below to fix it (i.e. pass the variable)? Here is my procedure
------------------------------
Sub ColorCell(

Range("D4").Selec

Select Case Numbe
Case
With Selection.Interio
.ColorIndex = 6 'Yello
.Pattern = xlSoli
End Wit

Case
With Selection.Interio
.ColorIndex = 10 'Gree
.Pattern = xlSoli
End Wit

Case
With Selection.Interio
.ColorIndex = 5 'Blu
.Pattern = xlSoli
End Wit

Case
With Selection.Interio
.ColorIndex = 3 'Re
.Pattern = xlSoli
End Wit

End Selec

End Su
---------------------------------------
Here is my Click Event

Sub CommandButton1_Click(
ColorCel
End Su
 
Scott said:
Dear All,

Thanks for your input. I still can't get it to work. What do I need to do to the code below to fix it (i.e. pass the variable)? Here is my procedure:
-------------------------------
Sub ColorCell()

Range("D4").Select

Select Case Number
Case 1
With Selection.Interior
.ColorIndex = 6 'Yellow
.Pattern = xlSolid
End With

Case 2
With Selection.Interior
.ColorIndex = 10 'Green
.Pattern = xlSolid
End With

Case 3
With Selection.Interior
.ColorIndex = 5 'Blue
.Pattern = xlSolid
End With

Case 4
With Selection.Interior
.ColorIndex = 3 'Red
.Pattern = xlSolid
End With

End Select

End Sub
----------------------------------------
Here is my Click Event:

Sub CommandButton1_Click()
ColorCell
End Sub
Click to expand...
Sub CommandButton1_Click()
ColorCell 1
End Sub

Sub ColorCell(ByVal i as Integer)
Range("D4").Select
Select Case i
... rest of code stays the same

In order to make it easier to follow the code, pass a string instead of
a number.
Sub CommandButton1_Click()
ColorCell "Red" 'or whatever color this is
End Sub

Sub ColorCell(ByVal s as String)
Range("D4").Select
Select Case s
Case "Red"
... rest of code stays essentially the same, just change the case
strings

Enjoy!
 
Back
Top