To hide UserForm command buttons

[jeff]

When UF5 is shown, the sub below will chk to see if certain cells are
not empty. If not, it will show the caption for each of the command
buttons (maximum of 10). It works fine, but what I want now is for the
command button not to be shown at all if the same condition occurs. I
tried adding another line in the THEN part below, but neither worked.
Any suggestions?
Thanks
j.o.

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
‘********************************************
‘tried adding these 2 lines, but neither worked
‘Me.Controls("CommandButton" & i).Visible = False
'UserForm5.Me.("CommandButton" & i).Visible=False
‘********************************************
Else
.Caption = VSws.Range("M" & i + 4).Value
.Visible = True
End If
End With
Next

End Sub

 

[JLGWhiz]

This worked for me:

Private Sub UserForm_Initialize()
For i = 1 To 3
If ActiveSheet.Range("B2").Value > "" Then
Me.Controls("CommandButton" & i).Visible = False
End If
Next
End Sub

I put three command buttons on a UF and used the initialize event to run a
For Next loop. When something was in B2 the buttons were hidden, when B2
was empty the buttons were visible.




When UF5 is shown, the sub below will chk to see if certain cells are
not empty. If not, it will show the caption for each of the command
buttons (maximum of 10). It works fine, but what I want now is for the
command button not to be shown at all if the same condition occurs. I
tried adding another line in the THEN part below, but neither worked.
Any suggestions?
Thanks
j.o.

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
‘********************************************
‘tried adding these 2 lines, but neither worked
‘Me.Controls("CommandButton" & i).Visible = False
'UserForm5.Me.("CommandButton" & i).Visible=False
‘********************************************
Else
.Caption = VSws.Range("M" & i + 4).Value
.Visible = True
End If
End With
Next

End Sub

 

[jeff]

This worked for me:

Private Sub UserForm_Initialize()
    For i = 1 To 3
      If ActiveSheet.Range("B2").Value > "" Then
         Me.Controls("CommandButton" & i).Visible = False
      End If
    Next
End Sub

I put three command buttons on a UF and used the initialize event to run a
For Next loop.  When something was in B2 the buttons were hidden, when B2
was empty the buttons were visible.


When UF5 is shown, the sub below will chk to see if certain cells are
not empty. If not, it will show the caption for each of the command
buttons (maximum of 10). It works fine, but what I want now is for the
command button not to be shown at all if the same condition occurs. I
tried adding another line in the THEN part below, but neither worked.
Any suggestions?
Thanks
j.o.

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
‘********************************************
‘tried adding these 2 lines, but neither worked
      ‘Me.Controls("CommandButton" & i).Visible = False
      'UserForm5.Me.("CommandButton" & i).Visible=False
‘********************************************
                Else
                    .Caption = VSws.Range("M" & i +4).Value
                    .Visible = True
                End If
            End With
    Next

End Sub

I do appreciate your help. Perhaps I didn't explain it properly.
Here's what it does right now. It looks at the range of 10 cells in
the Vehicle Summary sheet.
For each cell containing data, it will change the caption of the
corresponding command button in UserForm5 to = that cell value. If
nothing is in the cell, the button will still show up, but the caption
will be empty.
This works fine.

I'm just trying add a line in the current code that: if there is
nothing in the cell, the corresponding command button will be hidden.
I don't want empty command buttons to show up on the UserForm. I only
want the command buttons to show up that will have the caption changed
via data in the correcsonding cells.
Thanks
j.o.

 

[JLGWhiz]

I thought I understood what you needed. Maybe the way I answered it
confused you. Try this.

This should work. The If Then statement needs to be in the same loop with
your caption change so the condition is checked for the button on the same
loop as the criteria for the caption change is done.
This should avoid any inadvertant hiding of the buttons. After this runs,
only those buttons that had data in the cell should be showing. Note that
you have to add in the code for changing the caption, or just add the
addtional If Then statement into your existing code so it is structured the
same way.

Private Sub UserForm_Initialize()
For i = 1 To 10
'Your code to change caption here
If VSws.Range("H" & i + 4.Value = "" Then
Me.Controls("CommandButton" & i).Visible = False
End if
Next
End Sub

You could actually make it an If Then Else statement to change change the
caption, else hide the button.

This worked for me:

Private Sub UserForm_Initialize()
For i = 1 To 3
If ActiveSheet.Range("B2").Value > "" Then
Me.Controls("CommandButton" & i).Visible = False
End If
Next
End Sub

I put three command buttons on a UF and used the initialize event to run a
For Next loop. When something was in B2 the buttons were hidden, when B2
was empty the buttons were visible.


When UF5 is shown, the sub below will chk to see if certain cells are
not empty. If not, it will show the caption for each of the command
buttons (maximum of 10). It works fine, but what I want now is for the
command button not to be shown at all if the same condition occurs. I
tried adding another line in the THEN part below, but neither worked.
Any suggestions?
Thanks
j.o.

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
‘********************************************
‘tried adding these 2 lines, but neither worked
‘Me.Controls("CommandButton" & i).Visible = False
'UserForm5.Me.("CommandButton" & i).Visible=False
‘********************************************
Else
.Caption = VSws.Range("M" & i + 4).Value
.Visible = True
End If
End With
Next

End Sub

I do appreciate your help. Perhaps I didn't explain it properly.
Here's what it does right now. It looks at the range of 10 cells in
the Vehicle Summary sheet.
For each cell containing data, it will change the caption of the
corresponding command button in UserForm5 to = that cell value. If
nothing is in the cell, the button will still show up, but the caption
will be empty.
This works fine.

I'm just trying add a line in the current code that: if there is
nothing in the cell, the corresponding command button will be hidden.
I don't want empty command buttons to show up on the UserForm. I only
want the command buttons to show up that will have the caption changed
via data in the correcsonding cells.
Thanks
j.o.

 

[jeff]

I thought I understood what you needed.  Maybe the way I answered it
confused you.  Try this.

This should work.  The If Then statement needs to be in the same loop with
your caption change so the condition is checked for the button on the same
loop as the criteria for the caption change is done.
This should avoid any inadvertant hiding of the buttons.  After this runs,
only those buttons that had data in the cell should be showing.  Note that
you have to add in the code for changing the caption, or just add the
addtional If Then statement into your existing code so it is structured the
same way.

Private Sub UserForm_Initialize()
   For i = 1 To 10
   'Your code to change caption here
   If VSws.Range("H" & i + 4.Value = "" Then
     Me.Controls("CommandButton" & i).Visible = False
   End if
   Next
End Sub

You could actually make it an If Then Else statement to change change the
caption, else hide the button.












I do appreciate your help. Perhaps I didn't explain it properly.
Here's what it does right now. It looks at the range of 10 cells in
the Vehicle Summary sheet.
For each cell containing data, it will change the caption of the
corresponding command button in UserForm5 to = that cell value. If
nothing is in the cell, the button will still show up, but the caption
will be empty.
This works fine.

I'm just trying add a line in the current code that: if there is
nothing in the cell, the corresponding command button will be hidden.
I don't want empty command buttons to show up on the UserForm. I only
want the command buttons to show up that will have the caption changed
via data in the correcsonding cells.
Thanks
j.o.- Hide quoted text -

- Show quoted text -

This works great. As is often the case, I had a typo issue going on. I
appreciate your followup help.
Thanks
j.o.