Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Hi
is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Yes, you just need to know the name of the control. For this example, assume
the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Hi,
i get the error "object required" when i tried it. managed to find a way of setting the caption that works: ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete" but cant configure this to change the colour. any ideas? thankyou "Rick Rothstein (MVP - VB)" wrote: Yes, you just need to know the name of the control. For this example, assume the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Sub test()
Dim c1&, c2& c1 = RGB(51, 51, 153) c2 = RGB(153, 204, 255) With ActiveSheet.OLEObjects("checkbox1").Object ' change name .Value = Not .Value If .Value Then .BackColor = c2 .ForeColor = c1 Else .BackColor = c1 .ForeColor = c2 End If .Font.Bold = True End With End Sub Regards, Peter T "Patrick Bateman" wrote in message ... Hi, i get the error "object required" when i tried it. managed to find a way of setting the caption that works: ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete" but cant configure this to change the colour. any ideas? thankyou "Rick Rothstein (MVP - VB)" wrote: Yes, you just need to know the name of the control. For this example, assume the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Do you have more than one check box on different sheets with the same name?
Try this modification to my previous code (I just added ActiveSheet in front of the checkbox name and changed the referenced check box to CheckBox2 to match the code you showed in your last posting... again, change the name as required)... Sub Test() With ActiveSheet.CheckBox2 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub The above code does work on my system. However, if you are still having trouble with it, and if the code you posted is the only code that works for you, then you can do it this way.... Sub Test() With ActiveSheet.OLEObjects("CheckBox2").Object .Caption = "HELLO" .ForeColor = vbWhite .BackColor = vbRed End With End Sub Rick "Patrick Bateman" wrote in message ... Hi, i get the error "object required" when i tried it. managed to find a way of setting the caption that works: ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete" but cant configure this to change the colour. any ideas? thankyou "Rick Rothstein (MVP - VB)" wrote: Yes, you just need to know the name of the control. For this example, assume the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
Thanks peter that sorted it cheers!!
only thing is that it leaves the checkbox selected, how do i get the macro to leave the box unticked? "Peter T" wrote: Sub test() Dim c1&, c2& c1 = RGB(51, 51, 153) c2 = RGB(153, 204, 255) With ActiveSheet.OLEObjects("checkbox1").Object ' change name .Value = Not .Value If .Value Then .BackColor = c2 .ForeColor = c1 Else .BackColor = c1 .ForeColor = c2 End If .Font.Bold = True End With End Sub Regards, Peter T "Patrick Bateman" wrote in message ... Hi, i get the error "object required" when i tried it. managed to find a way of setting the caption that works: ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete" but cant configure this to change the colour. any ideas? thankyou "Rick Rothstein (MVP - VB)" wrote: Yes, you just need to know the name of the control. For this example, assume the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checkbox from control toolbar macro
The demo toggled the ticked value and colours only for illustration, remove
it from your own code. The code did nothing with respect to (de)Select. Ensure you exit design mode. Rick's example should also work fine. Regards, Peter T "Patrick Bateman" wrote in message ... Thanks peter that sorted it cheers!! only thing is that it leaves the checkbox selected, how do i get the macro to leave the box unticked? "Peter T" wrote: Sub test() Dim c1&, c2& c1 = RGB(51, 51, 153) c2 = RGB(153, 204, 255) With ActiveSheet.OLEObjects("checkbox1").Object ' change name .Value = Not .Value If .Value Then .BackColor = c2 .ForeColor = c1 Else .BackColor = c1 .ForeColor = c2 End If .Font.Bold = True End With End Sub Regards, Peter T "Patrick Bateman" wrote in message ... Hi, i get the error "object required" when i tried it. managed to find a way of setting the caption that works: ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete" but cant configure this to change the colour. any ideas? thankyou "Rick Rothstein (MVP - VB)" wrote: Yes, you just need to know the name of the control. For this example, assume the name is CheckBox1 (the default name Excel will give to the first check box).... Sub Test() With CheckBox1 .BackColor = vbRed .ForeColor = vbWhite .Caption = "HELLO" End With End Sub Run the above macro and the check box will read HELLO in white letters on a red background. You can modify almost all of the controls (any control you place, not just a check box) using the structure above. Rick "Patrick Bateman" wrote in message ... Hi is it possible to create a macro to change the colour and caption properties of a checkbox inserted friom the control toolbar? thankyou for your help Patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form Toolbar verses Control Toolbar | Excel Discussion (Misc queries) | |||
Add-In Toolbar Control - Removing The Toolbar Question | Excel Programming | |||
Control Toolbox Checkbox Macro - Help Please! | Excel Programming | |||
Showing MsgBox after Control Toolbar macro runs | Excel Programming | |||
VBA Control Checkbox | Excel Programming |