trying to hide command buttons in userform if cell empty

[wpreqq99]

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

 

[john]

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

 

[JLGWhiz]

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


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

 

[JLGWhiz]

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


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

 

[wpreqq99]

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

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.......

 

[john]

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

 

[wpreqq99]

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

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