|
How to get buttons on floating control to pick up colour
Hi
I have created a control with 8 command buttons, for the user to be able to quickly format cells according to the background colour and font colour of the button clicked. I want to be able to pick up the colours and a caption from a setup sheet, but it isn't working. The first 4 buttons are selecting the colours set for the last 4, and the last 4 are left unaltered. No captions are being picked up to display on each button. Any clues as to what I am doing wrong - I have been going round in circles all morning, and can't see the error. Private Sub Userform_Initialize() On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i as long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vacapt = Sheets("Colors").Range("H4:H11").Value i = 0 btncount = Sheets("Colors").Range("E1").Value 'this counts the number of cells with values in E4:E11 For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1): btnColors2 = vaFore(i, 1):btnCaption = vaCapt(i,1) ReDim Preserve Buttons(1 To 8) Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? End If i = i + 1 Next ctrl End Sub -- Regards Roger Govier |
How to get buttons on floating control to pick up colour
Hi Roger,
Is Buttons an array for a class you have created to handle to click event in a single place rather than each controls click event? Is the For Each ctrl doing the controls in the order you think or want? I have added a line to load caption, which works for me, assuming the captions are where you think they are, in column H. For a userform with 8 commandbuttons on and the following data in E4:H11 I can get the code below to functions as expected. 6 4 One 38 3 Two 4 2 Three 3 5 Four 33 36 Five 5 3 Six 3 50 Seven 4 34 Eight Private Sub Userform_Initialize() ' On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, _ btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i As Long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vaCapt = Sheets("Colors").Range("H4:H11").Value ' i=0 generates subscript error i = 1 btncount = Sheets("Colors").Range("E1").Value 'this counts _ the number of cells with values in E4:E11 ' move from within loop. ReDim Preserve Buttons(1 To 8) For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1) btnColors2 = vaFore(i, 1) btncaption = vaCapt(i, 1) Set Buttons(btn) = New Class1 Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) Buttons(btn).ColorButtons.Caption _ = btncaption End If i = i + 1 Next ctrl End Sub Cheers Andy Roger Govier wrote: Hi I have created a control with 8 command buttons, for the user to be able to quickly format cells according to the background colour and font colour of the button clicked. I want to be able to pick up the colours and a caption from a setup sheet, but it isn't working. The first 4 buttons are selecting the colours set for the last 4, and the last 4 are left unaltered. No captions are being picked up to display on each button. Any clues as to what I am doing wrong - I have been going round in circles all morning, and can't see the error. Private Sub Userform_Initialize() On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i as long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vacapt = Sheets("Colors").Range("H4:H11").Value i = 0 btncount = Sheets("Colors").Range("E1").Value 'this counts the number of cells with values in E4:E11 For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1): btnColors2 = vaFore(i, 1):btnCaption = vaCapt(i,1) ReDim Preserve Buttons(1 To 8) Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? End If i = i + 1 Next ctrl End Sub -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
How to get buttons on floating control to pick up colour
Just use
ctrl.Caption = btncaption BTW, I think you should initialise i to 1. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Roger Govier" wrote in message ... Hi I have created a control with 8 command buttons, for the user to be able to quickly format cells according to the background colour and font colour of the button clicked. I want to be able to pick up the colours and a caption from a setup sheet, but it isn't working. The first 4 buttons are selecting the colours set for the last 4, and the last 4 are left unaltered. No captions are being picked up to display on each button. Any clues as to what I am doing wrong - I have been going round in circles all morning, and can't see the error. Private Sub Userform_Initialize() On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i as long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vacapt = Sheets("Colors").Range("H4:H11").Value i = 0 btncount = Sheets("Colors").Range("E1").Value 'this counts the number of cells with values in E4:E11 For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1): btnColors2 = vaFore(i, 1):btnCaption = vaCapt(i,1) ReDim Preserve Buttons(1 To 8) Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? End If i = i + 1 Next ctrl End Sub -- Regards Roger Govier |
How to get buttons on floating control to pick up colour
Hi Andy
Many thanks for the response. I already have a class called ClassColors handling the click event, so I had to remove your line Set Buttons(btn) = New Class1 I also needed to move the i=i+1 outside of the loop, as there are more controls on my form, which were not colour buttons. I now realise, that was why I was getting the wrong colours allocated to the buttons. Buttons(btn).ColorButtons.Caption = btncaption did the trick perfectly. Why can one never see these things until others point them out!!! Sanity has returned - I am extremely grateful. -- Regards Roger Govier "Andy Pope" wrote in message ... Hi Roger, Is Buttons an array for a class you have created to handle to click event in a single place rather than each controls click event? Is the For Each ctrl doing the controls in the order you think or want? I have added a line to load caption, which works for me, assuming the captions are where you think they are, in column H. For a userform with 8 commandbuttons on and the following data in E4:H11 I can get the code below to functions as expected. 6 4 One 38 3 Two 4 2 Three 3 5 Four 33 36 Five 5 3 Six 3 50 Seven 4 34 Eight Private Sub Userform_Initialize() ' On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, _ btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i As Long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vaCapt = Sheets("Colors").Range("H4:H11").Value ' i=0 generates subscript error i = 1 btncount = Sheets("Colors").Range("E1").Value 'this counts _ the number of cells with values in E4:E11 ' move from within loop. ReDim Preserve Buttons(1 To 8) For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1) btnColors2 = vaFore(i, 1) btncaption = vaCapt(i, 1) Set Buttons(btn) = New Class1 Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) Buttons(btn).ColorButtons.Caption _ = btncaption End If i = i + 1 Next ctrl End Sub Cheers Andy Roger Govier wrote: Hi I have created a control with 8 command buttons, for the user to be able to quickly format cells according to the background colour and font colour of the button clicked. I want to be able to pick up the colours and a caption from a setup sheet, but it isn't working. The first 4 buttons are selecting the colours set for the last 4, and the last 4 are left unaltered. No captions are being picked up to display on each button. Any clues as to what I am doing wrong - I have been going round in circles all morning, and can't see the error. Private Sub Userform_Initialize() On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i as long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vacapt = Sheets("Colors").Range("H4:H11").Value i = 0 btncount = Sheets("Colors").Range("E1").Value 'this counts the number of cells with values in E4:E11 For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1): btnColors2 = vaFore(i, 1):btnCaption = vaCapt(i,1) ReDim Preserve Buttons(1 To 8) Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? End If i = i + 1 Next ctrl End Sub -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
How to get buttons on floating control to pick up colour
Hi Bob
You are absolutely right. See also my response to Andy. -- Regards Roger Govier "Bob Phillips" wrote in message ... Just use ctrl.Caption = btncaption BTW, I think you should initialise i to 1. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Roger Govier" wrote in message ... Hi I have created a control with 8 command buttons, for the user to be able to quickly format cells according to the background colour and font colour of the button clicked. I want to be able to pick up the colours and a caption from a setup sheet, but it isn't working. The first 4 buttons are selecting the colours set for the last 4, and the last 4 are left unaltered. No captions are being picked up to display on each button. Any clues as to what I am doing wrong - I have been going round in circles all morning, and can't see the error. Private Sub Userform_Initialize() On Error Resume Next Dim ctrl As Control, btnColors As Long, btn As Long, btnColors2 As Long, btn2 As Long Dim vaFore As Variant, vaBack As Variant, vaCapt As Variant Dim btncaption As String, btncount As Long, i as long vaBack = Sheets("Colors").Range("E4:E11").Value vaFore = Sheets("Colors").Range("F4:F11").Value vacapt = Sheets("Colors").Range("H4:H11").Value i = 0 btncount = Sheets("Colors").Range("E1").Value 'this counts the number of cells with values in E4:E11 For Each ctrl In frmcolours2.Controls If ctrl.Tag = "Color Button" Then btn = i btnColors = vaBack(i, 1): btnColors2 = vaFore(i, 1):btnCaption = vaCapt(i,1) ReDim Preserve Buttons(1 To 8) Set Buttons(btn).ColorButtons = ctrl Buttons(btn).ColorButtons.BackColor _ = ThisWorkbook.Colors(btnColors) Buttons(btn).ColorButtons.ForeColor _ = ThisWorkbook.Colors(btnColors2) ' what need to be inserted here to write the Caption to the button? End If i = i + 1 Next ctrl End Sub -- Regards Roger Govier |
| All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com