trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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

trying to hide command buttons in userform if cell empty
 

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