Multipage Buttons??

  • Thread starter TotallyConfused
  • Start date
T

TotallyConfused

I am wondering if you can make the buttons/tabs on a multipage in an Excel
Userform different color when you click on it???? Can someone please help me
with this? Thank you very much.
 
J

JLGWhiz

Put a button from Control Toolbox on Sheet1 and put the following code in
the sheet code module.

Private Sub CommandButton1_Click()
Dim cb1 As CommandButton
Set cb1 = Sheets(1).CommandButton1
If cb1.BackColor = &H80FF80 Then
Sheets(1).CommandButton1.BackColor = &HFFFF80
Else
Sheets(1).CommandButton1.BackColor = &H80FF80
End If
End Sub


Be sure you are not in design mode and the button should change from green
to blue and vice versa.
 
T

TotallyConfused

I am sorry for being stupid with this but I am new to Excel code. If I
create command button from the Control Toolbox do I put this command button
over one of the tabs? I have over 10 pages in my multipage tool in my
userform. Also for multipage, they are identified as Page1 etc. If I
change the code below to read Page(1) will the code still work? Thank you.
 
J

JLGWhiz

I would just put the button on the UserForm as a separate control and change
the code as follows:

Private Sub CommandButton1_Click()
Dim cb1 As CommandButton
Set cb1 = Me.CommandButton1
If cb1.BackColor = &H80FF80 Then
cb1.BackColor = &HFFFF80
Else
cb1.BackColor = &H80FF80
End If

'Add additional code here if needed to perform an action.

End Sub

The Me constant refers to the object that holds the code, which in this case
would be the UserForm. It is assumed that you would want the some action to
be performed when the button is clicked so you can add that in beneath the
code to change colors. You can also change the If statement to set some
other criteria to make the color change, I only used the paricular wording
to show how it is done. You can also get different color codes by opening
the properties window for the button (design mode and right click button)
and click on backcolor>palette. Click a color to use then copy the code
from there to the macro. Or you can go through the agony of trying to set
RGB colors.
 
T

TotallyConfused

Thank you this works. I will have to set the multipage with "no Tabs", add
command buttons to change color when I click. However, I have to open the
page. HHow do I write the code to add to the code below to open the page?
Thank you.
 
J

JLGWhiz

You can play with this for a while. Maybe you can get some ideas on how it
works.
You need a UserForm with a command button from the toolbox and a multipage
control.
The code goes behind the UserForm. To access the UserForm code module,
double click on the form in design mode and the code module will display.
Copy and paste the code below:

Private Sub CommandButton1_Click()
Dim cb1 As CommandButton
Set cb1 = Me.CommandButton1
If Me.MultiPage1.Pages(0).Caption = "Hello" Then
cb1.BackColor = &HFFFF80
Else
cb1.BackColor = &H80FF80
Me.MultiPage1.Pages(1).Caption = "Hello"
End If
End Sub


Private Sub MultiPage1_Change()
Dim cb1 As CommandButton
Set cb1 = Me.CommandButton1
If Me.MultiPage1.Pages(1).Caption = "Hello" Then
Me.MultiPage1.Pages(1).Caption = ""
Me.MultiPage1.Pages(0).Caption = "Bye"
Me.MultiPage1.Value = 1
cb1.BackColor = &HFFFF80
End If


Then, in the Project window, double click on Module1 and put this code in:

Sub macRun()
UserForm1.Show
End Sub

You can run this sub to show the UserForm and then click the button and the
multipage tabs.
To close the UserForm, click the big X at upper right.
 
T

TotallyConfused

Thank you again. I apologize for my ignorance. I have accomplished changing
the colors of the buttons. However, when I press the button it turns color
but does not open the page. When I click on another button I need the
previous button to go back to original color. I don't think I am doing this
with the following code. Can you please help.? Thank you

Private Sub MultiPage1_Change()
Dim cb1 As CommandButton
Set cb1 = Me.CommandButton52
If Me.MultiPage1.Pages(14).Caption = "Hello" Then
Me.MultiPage1.Pages(14).Caption = ""
Me.MultiPage1.Pages(0).Caption = "Bye"
Me.MultiPage1.Value = 1
cb1.BackColor = &HFFFF80
End If
Set cb1 = Me.CommandButton53
If Me.MultiPage1.Pages(15).Caption = Visible = True Then
Me.MultiPage1.Pages(15).Caption = ""
Me.MultiPage1.Pages(0).Caption = "Visible = False"
Me.MultiPage1.Value = 1
cb1.BackColor = &HFFFF80
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top