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


"(E-Mail Removed)" 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 <j...@discussions.microsoft.com> 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


"(E-Mail Removed)" wrote:

> On May 11, 7:37 am, john <j...@discussions.microsoft.com> 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 <j...@discussions.microsoft.com> 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 thecode
> 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
>
>
>
> "wpreq...@yahoo.com" wrote:
> > On May 11, 7:37 am, john <j...@discussions.microsoft.com> 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