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

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

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

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

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