I'm trying to have 6 command buttons within a userform show a cell
range when the userform comes up. I've put this code in the UF module.
When I bring up the UF, nothing shows. Do I have to change a setting
in the UF properties? Thanks, jeff

Private Sub UserForm_Click()
CommandButton1.Caption = Range("L5").Value
CommandButton2.Caption = Range("L6").Value
CommandButton3.Caption = Range("L7").Value
CommandButton4.Caption = Range("L8").Value
CommandButton5.Caption = Range("L9").Value
CommandButton6.Caption = Range("L10").Value
End Sub

paste this code behind your form:

Private Sub UserForm_Initialize()
For i = 1 To 5

With ActiveSheet

Me.Controls("CommandButton" & i).Caption = _
.Range("L" & i + 4).Value

End With

Next
End Sub

--
jb


" wrote:

I'm trying to have 6 command buttons within a userform show a cell
range when the userform comes up. I've put this code in the UF module.
When I bring up the UF, nothing shows. Do I have to change a setting
in the UF properties? Thanks, jeff

Private Sub UserForm_Click()
CommandButton1.Caption = Range("L5").Value
CommandButton2.Caption = Range("L6").Value
CommandButton3.Caption = Range("L7").Value
CommandButton4.Caption = Range("L8").Value
CommandButton5.Caption = Range("L9").Value
CommandButton6.Caption = Range("L10").Value
End Sub

On May 11, 7:37*am, john wrote:
paste this code behind your form:

Private Sub UserForm_Initialize()
* * For i = 1 To 5

* * * * With ActiveSheet

* * * * * * Me.Controls("CommandButton" & i).Caption= _
* * * * * * .Range("L" & i + 4).Value

* * * * End With

* * Next
End Sub

--
jb


Thanks jb!!
I don't understand why the 4 is in the last line. I made 10 buttons
all together, so I changed the coding to reflect this. At 1st, I
changed the 5 in the 1st line to 10, and the 4 to a 9. That was the
logic I saw. It didn't work. When I left the 4 alone, it worked fine
for all 10 buttons. I'll have to learn more about this to know why
that 4 works with all 10 buttons.
I appreciate your time and effort.
thanks
jeff

You are starting in row 5 so i which starts at a value of 1 needs to be
increased to select correct row. Therefore 1 + 4 = 5 then each time the code
loops i is incremented by 1 which returns the next rows value.

If you add more buttons but starting row remains the same, just increase the
last number of the For loop to match - in your new case, the value is 10.

--
jb


" wrote:

On May 11, 7:37 am, john wrote:
paste this code behind your form:

Private Sub UserForm_Initialize()
For i = 1 To 5

With ActiveSheet

Me.Controls("CommandButton" & i).Caption= _
.Range("L" & i + 4).Value

End With

Next
End Sub

--
jb


Thanks jb!!
I don't understand why the 4 is in the last line. I made 10 buttons
all together, so I changed the coding to reflect this. At 1st, I
changed the 5 in the 1st line to 10, and the 4 to a 9. That was the
logic I saw. It didn't work. When I left the 4 alone, it worked fine
for all 10 buttons. I'll have to learn more about this to know why
that 4 works with all 10 buttons.
I appreciate your time and effort.
thanks
jeff

On May 11, 9:01*am, john wrote:
You are starting in row 5 so i which starts at a value of 1 needs to be
increased to select correct row. Therefore 1 + 4 = 5 then each time the code
loops i is incremented by 1 which returns the next rows value.

If you add more buttons but starting row remains the same, just increase the
last number of the For loop to match - in your new case, the value is 10.

--
jb



" wrote:
On May 11, 7:37 am, john wrote:
paste this code behind your form:

Private Sub UserForm_Initialize()
* * For i = 1 To 5

* * * * With ActiveSheet

* * * * * * Me.Controls("CommandButton" & i).Caption= _
* * * * * * .Range("L" & i + 4).Value

* * * * End With

* * Next
End Sub

--
jb


I got it.... thanks