I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet “Vehicle summary”.
This For-next loop works fine.

For i = 1 To 10
' With Sheets("Vehicle summary")
' Me.Controls("CommandButton" & i).Caption = _
' .Range("L" & i + 4).Value
' End With
' Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I haven’t been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didn’t work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
.CommandButton1.Visible = False

Thanks,
jeff

try this.

Private Sub UserForm_Initialize()
For i = 1 To 10

With Sheets("Vehicle summary")


With Me.Controls("CommandButton" & i)

If .Range("H" & i + 4).Value = "" Then

.Visible = False

Else

.Caption = .Range("L" & i + 4).Value

End If

End With

End With
Next

End Sub
--
jb


" wrote:

I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet €œVehicle summary€�.
This For-next loop works fine.

For i = 1 To 10
' With Sheets("Vehicle summary")
' Me.Controls("CommandButton" & i).Caption = _
' .Range("L" & i + 4).Value
' End With
' Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I havent been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didnt work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
.CommandButton1.Visible = False

Thanks,
jeff

Something like this:

Private Sub UserForm_Initialize()
For i = 1 To 2
If Range("H" & i + 4) "" Then
Me.Controls("CommandButton" & i).Visible = True
Else
Me.Controls("CommandButton" & i).Visible = False
End If
Next
End Sub


wrote in message
...
I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet “Vehicle summary”.
This For-next loop works fine.

For i = 1 To 10
' With Sheets("Vehicle summary")
' Me.Controls("CommandButton" & i).Caption = _
' .Range("L" & i + 4).Value
' End With
' Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I haven’t been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didn’t work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
.CommandButton1.Visible = False

Thanks,
jeff

You probably want all 10 checked.

Private Sub UserForm_Initialize()
For i = 1 To 10
If Range("H" & i + 4) "" Then
Me.Controls("CommandButton" & i).Visible = True
Else
Me.Controls("CommandButton" & i).Visible = False
End If
Next
End Sub


wrote in message
...
I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet “Vehicle summary”.
This For-next loop works fine.

For i = 1 To 10
' With Sheets("Vehicle summary")
' Me.Controls("CommandButton" & i).Caption = _
' .Range("L" & i + 4).Value
' End With
' Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I haven’t been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didn’t work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
.CommandButton1.Visible = False

Thanks,
jeff

On May 11, 2:34*pm, john wrote:
try this.

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

* * * * With Sheets("Vehicle summary")

* * * * * * With Me.Controls("CommandButton" & i)

* * * * * * * * If .Range("H" & i + 4).Value = "" Then

* * * * * * * * * * .Visible = False

* * * * * * * * Else

* * * * * * * * * * .Caption = .Range("L" & i + 4).Value

* * * * * * * * End If

* * * * * * End With

* * * * End With
* * Next

End Sub
--
jb



" wrote:
I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet “Vehicle summary”.
This For-next loop works fine.

For i = 1 To 10
*' * * * * * With Sheets("Vehicle summary")
*' * * * * * Me.Controls("CommandButton" & i).Caption = _
*' * * * * *.Range("L" & i + 4).Value
*' * * * End With
*' * Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I haven’t been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didn’t work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
* * .CommandButton1.Visible = False

Thanks,
jeff- Hide quoted text -


Thanks. I tried it, but get an 438 run-time error. Object doesn't
support this property or method. I'm playing around with it. Maybe it
will make sense to you.......

whoops - sorry, that won't work!

hopefully, this will!

Private Sub UserForm_Initialize()
Dim VSws As Worksheet

Set VSws = Sheets("Vehicle summary")
For i = 1 To 10


With Me.Controls("CommandButton" & i)

If VSws.Range("H" & i + 4).Value = "" Then

.Visible = False

Else

.Caption = VSws.Range("L" & i + 4).Value

.Visible = True

End If

End With

Next

End Sub



--
jb


" wrote:

I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet €œVehicle summary€�.
This For-next loop works fine.

For i = 1 To 10
' With Sheets("Vehicle summary")
' Me.Controls("CommandButton" & i).Caption = _
' .Range("L" & i + 4).Value
' End With
' Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I havent been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didnt work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
.CommandButton1.Visible = False

Thanks,
jeff

On May 11, 2:53*pm, john wrote:
whoops - sorry, that won't work!

hopefully, this will!

Private Sub UserForm_Initialize()
Dim VSws As Worksheet

Set VSws = Sheets("Vehicle summary")
* * For i = 1 To 10

* * * * * * With Me.Controls("CommandButton" & i)

* * * * * * * * If VSws.Range("H" & i + 4).Value = "" Then

* * * * * * * * * * .Visible = False

* * * * * * * * Else

* * * * * * * * * * .Caption = VSws.Range("L" & i + 4).Value

* * * * * * * * * * .Visible = True

* * * * * * * * End If

* * * * * * End With

* * Next

End Sub

--
jb



" wrote:
I have 10 Command buttons in a Userform. (they are numbered 1-10) This
morning, I got help on changing the captions for the buttons based on
the cell values in L5 through L14 in sheet “Vehicle summary”.
This For-next loop works fine.

For i = 1 To 10
*' * * * * * With Sheets("Vehicle summary")
*' * * * * * Me.Controls("CommandButton" & i).Caption = _
*' * * * * *.Range("L" & i + 4).Value
*' * * * End With
*' * Next

The problem is that not all 10 will have values all the time.
I decided that having blank buttons showing up on the form might be
confusing. So, I wanted them hidden if there was no value in the
cells. Column L in the range above contains formulas. But column H of
each line will only be blank if there is nothing.
So, if any cells from H5 through H14 is empty, then that button will
be hidden. I thought the coding I have below would be similar to what
I would need. But, I haven’t been able to figure out how to integrate
this with the above coding. I would guess that a For-each could be set
up to handle what I have below instead of repeating it 10 times for
each of the cells (which I also tried just to have it done, but it
didn’t work). As always, any help is appreciated.

If IsEmpty(Range("H5")) Then
* * .CommandButton1.Visible = False

Thanks,
jeff- Hide quoted text -


This one works perfectly, jb!
I really appreciate the time and help.
Thanks to JLGWhiz, too. The 2nd one didn't error out, but the captions
were not correct. I appreciate the effort.
jeff