Hide Buttons Selectively

J

jues

New Member
Joined
Mar 17, 2023
Messages
15
Office Version
  1. 365
Platform
  1. Windows
Good morning friends I hope you are well

Within the VTL Form (frmvtl) I have three buttons with identical icons CommandButton5 (Guardar), CommandButton19 (Agregar) and CommandButton20 (Editar) which I need to be selectively hidden when the following conditions are met after clicking the Search button :

VBA Code: 
Private Sub CmdBUSCAR_Click()

 

    'Alertas del Sistema

    If Me.TextBox1.Value = "" Then

        Msgbox "Datos Incompletos: Ingrese un Número de Cédula", vbExclamation

    End If

 

    'Buscar Datos

    Dim sh As Worksheet

    Dim f As Range, r As Range

    Dim cell As String

    Dim fecIni As Date, fecFin As Date

   

    Set sh = Sheets("MATRIZ4")

    Set r = sh.Range("B:B")

   

    If TextBox4.Value = "" Then

      Set f = r.Find(Val(TextBox1.Value), , xlValues, xlWhole, xlByRows, xlNext)

      If Not f Is Nothing Then

          Me.TextBox2.Value = sh.Range("C" & f.Row).Value

          Me.ComboBox1.Value = sh.Range("F" & f.Row).Value

          Me.ComboBox2.Value = sh.Range("G" & f.Row).Value

          Me.TextBox36.Value = sh.Range("AN" & f.Row).Value

          Me.TextBox33.Value = sh.Range("AV" & f.Row).Value

          Me.TextBox34.Value = sh.Range("AW" & f.Row).Value

          Me.TextBox35.Value = sh.Range("AX" & f.Row).Value

      End If

    Else

      If IsDate(TextBox4.Value) And IsDate(TextBox5.Value) Then

          fecIni = CDate(TextBox4.Value)

          fecFin = CDate(TextBox5.Value)

            Set f = r.Find(Val(TextBox1.Value), , xlValues, xlWhole, xlByRows, xlNext)

            If Not f Is Nothing Then

            cell = f.Address

            Do

              If sh.Range("D" & f.Row).Value = fecIni And sh.Range("E" & f.Row).Value = fecFin Then

                Me.TextBox2.Value = sh.Range("C" & f.Row).Value

                Me.ComboBox1.Value = sh.Range("F" & f.Row).Value

                Me.ComboBox2.Value = sh.Range("G" & f.Row).Value

                Me.Label32 = sh.Range("H" & f.Row).Value

                Me.TextBox16.Value = sh.Range("AD" & f.Row).Value

                Me.TextBox17.Value = sh.Range("AE" & f.Row).Value

                Me.TextBox18.Value = sh.Range("AF" & f.Row).Value

                Me.TextBox19.Value = sh.Range("AG" & f.Row).Value

                Me.TextBox20.Value = sh.Range("AH" & f.Row).Value

                Me.TextBox21.Value = sh.Range("AI" & f.Row).Value

                Me.TextBox22.Value = sh.Range("AJ" & f.Row).Value

                Me.TextBox23.Value = sh.Range("AL" & f.Row).Value

                Me.TextBox25.Value = sh.Range("AK" & f.Row).Value

                Me.TextBox26.Value = sh.Range("AM" & f.Row).Value

                Me.TextBox27.Value = sh.Range("AO" & f.Row).Value

                Me.TextBox28.Value = sh.Range("AP" & f.Row).Value

                Me.TextBox29.Value = sh.Range("AQ" & f.Row).Value

                Me.TextBox30.Value = sh.Range("AR" & f.Row).Value

                Me.TextBox31.Value = sh.Range("AT" & f.Row).Value

                Me.TextBox32.Value = sh.Range("AU" & f.Row).Value

                Me.TextBox33.Value = sh.Range("AV" & f.Row).Value

                Me.TextBox34.Value = sh.Range("AW" & f.Row).Value

                Me.TextBox35.Value = sh.Range("AX" & f.Row).Value

                Me.TextBox36.Value = sh.Range("AN" & f.Row).Value

              Exit Sub

              End If

              Set f = r.FindNext(f)

            Loop While f.Address <> cell

          End If

       End If

    End If

   

    'Boton Guardar Visible

    If Me.TextBox2.Value <> "" And Me.ComboBox1.Value <> "" And Me.ComboBox2.Value <> "" And Me.TextBox16.Value = "" And _

        Me.TextBox17.Value = "" And Me.TextBox18.Value = "" And Me.TextBox19.Value = "" And Me.TextBox20.Value = "" And _

        Me.TextBox21.Value = "" And Me.TextBox22.Value = "" And Me.TextBox23.Value <> "" And Me.TextBox24.Value <> "" And _

        Me.TextBox25.Value = "" And Me.TextBox26.Value = "" And Me.TextBox27.Value = "" And Me.TextBox28.Value = "" And _

        Me.TextBox29.Value = "" And Me.TextBox30.Value = "" And Me.TextBox31.Value = "" And Me.TextBox32.Value <> "" And _

        Me.TextBox33.Value <> "" And Me.TextBox34.Value <> "" And Me.TextBox35.Value <> "" And Me.TextBox36.Value <> "" Then

        CommandButton5.Visible = True

        CommandButton19.Visible = False

        CommandButton20.Visible = False

 

    'Boton Agregar Visible

    ElseIf Me.TextBox2.Value = "" And Me.ComboBox1.Value = "" And Me.ComboBox2.Value = "" And Me.TextBox16.Value = "" And _

            Me.TextBox17.Value = "" And Me.TextBox18.Value = "" And Me.TextBox19.Value = "" And Me.TextBox20.Value = "" And _

            Me.TextBox21.Value = "" And Me.TextBox22.Value = "" And Me.TextBox23.Value = "" And Me.TextBox24.Value = "" And _

            Me.TextBox25.Value = "" And Me.TextBox26.Value = "" And Me.TextBox27.Value = "" And Me.TextBox28.Value = "" And _

            Me.TextBox29.Value = "" And Me.TextBox30.Value = "" And Me.TextBox31.Value = "" And Me.TextBox32.Value = "" And _

            Me.TextBox33.Value = "" And Me.TextBox34.Value = "" And Me.TextBox35.Value = "" And Me.TextBox36.Value = "" Then

            CommandButton5.Visible = False

            CommandButton19.Visible = True

            CommandButton20.Visible = False

 

    'Boton Editar Visible

    ElseIf Me.TextBox2.Value <> "" And Me.ComboBox1.Value <> "" And Me.ComboBox2.Value <> "" And Me.TextBox23.Value <> "" And _

            Me.TextBox24.Value <> "" And Me.TextBox25.Value <> "" And Me.TextBox26.Value <> "" And Me.TextBox27.Value <> "" And _

            Me.TextBox28.Value <> "" And Me.TextBox29.Value <> "" And Me.TextBox30.Value <> "" And Me.TextBox31.Value <> "" And _

            Me.TextBox32.Value <> "" And Me.TextBox33.Value <> "" And Me.TextBox34.Value <> "" And Me.TextBox35.Value <> "" And _

            Me.TextBox36.Value <> "" Then

            CommandButton5.Visible = False

            CommandButton19.Visible = False

            CommandButton20.Visible = True

   

    Else

        CommandButton5.Visible = False

        CommandButton19.Visible = False

        CommandButton20.Visible = False

    End If

End Sub

VBA Code: 
Private Sub CmdBUSCAR_Click()
'Botón Guardar Visible
    If (Me.TextBox2.Value <> "" And Me.ComboBox1.Value <> "" And Me.ComboBox2.Value <> "" And Me.TextBox16.Value = "" And _
        Me.TextBox17.Value = "" And Me.TextBox18.Value = "" And Me.TextBox19.Value = "" And Me.TextBox20.Value = "" And _
        Me.TextBox21.Value = "" And Me.TextBox22.Value = "" And Me.TextBox23.Value <> "" And Me.TextBox24.Value <> "" And _
        Me.TextBox25.Value = "" And Me.TextBox26.Value = "" And Me.TextBox27.Value = "" And Me.TextBox28.Value = "" And _
        Me.TextBox29.Value = "" And Me.TextBox30.Value = "" And Me.TextBox31.Value = "" And Me.TextBox32.Value <> "" And _
        Me.TextBox33.Value <> "" And Me.TextBox34.Value <> "" And Me.TextBox35.Value <> "" And Me.TextBox36.Value <> "") Then
        CommandButton5.Visible = True
        CommandButton19.Visible = False
        CommandButton20.Visible = False

    'Botón Agregar Visible
    ElseIf (Me.TextBox2.Value = "" And Me.ComboBox1.Value = "" And Me.ComboBox2.Value = "" And Me.TextBox16.Value = "" And _
            Me.TextBox17.Value = "" And Me.TextBox18.Value = "" And Me.TextBox19.Value = "" And Me.TextBox20.Value = "" And _
            Me.TextBox21.Value = "" And Me.TextBox22.Value = "" And Me.TextBox23.Value = "" And Me.TextBox24.Value = "" And _
            Me.TextBox25.Value = "" And Me.TextBox26.Value = "" And Me.TextBox27.Value = "" And Me.TextBox28.Value = "" And _
            Me.TextBox29.Value = "" And Me.TextBox30.Value = "" And Me.TextBox31.Value = "" And Me.TextBox32.Value = "" And _
            Me.TextBox33.Value = "" And Me.TextBox34.Value = "" And Me.TextBox35.Value = "" And Me.TextBox36.Value = "") Then
            CommandButton5.Visible = False
            CommandButton19.Visible = True
            CommandButton20.Visible = False

    'Botón Editar Visible
    ElseIf Me.TextBox2.Value <> "" And Me.ComboBox1.Value <> "" And Me.ComboBox2.Value <> "" And Me.TextBox23.Value <> "" And _
            Me.TextBox24.Value <> "" And Me.TextBox25.Value <> "" And Me.TextBox26.Value <> "" And Me.TextBox27.Value <> "" And _
            Me.TextBox28.Value <> "" And Me.TextBox29.Value <> "" And Me.TextBox30.Value <> "" And Me.TextBox31.Value <> "" And _
            Me.TextBox32.Value <> "" And Me.TextBox33.Value <> "" And Me.TextBox34.Value <> "" And Me.TextBox35.Value <> "" And _
            Me.TextBox36.Value <> "" Then
            CommandButton5.Visible = False
            CommandButton19.Visible = False
            CommandButton20.Visible = True

    Else
        CommandButton5.Visible = False
        CommandButton19.Visible = False
        CommandButton20.Visible = False
    End If
End Sub

Currently I have managed to hide the “Guardar” (CommandButton5) and “Agregar” (CommandButton19) buttons, however, when doing a combined search with ID Number (TextBox1), Start Date (TextBox4) and End Date (TextBox5) using “Buscar” button (CmdBUSCAR), the “Guardar” (CommandButton5) and “Agregar” (CommandButton19) buttons are not hidden, leaving the three buttons even if the condition is met so that only the “Editar” button (CommandButton20) can be visible:

Code: 
Private Sub CmdBUSCAR_Click()

    'Boton Editar Visible

    ElseIf Me.TextBox2.Value <> "" And Me.ComboBox1.Value <> "" And Me.ComboBox2.Value <> "" And Me.TextBox23.Value <> "" And _

            Me.TextBox24.Value <> "" And Me.TextBox25.Value <> "" And Me.TextBox26.Value <> "" And Me.TextBox27.Value <> "" And _

            Me.TextBox28.Value <> "" And Me.TextBox29.Value <> "" And Me.TextBox30.Value <> "" And Me.TextBox31.Value <> "" And _

            Me.TextBox32.Value <> "" And Me.TextBox33.Value <> "" And Me.TextBox34.Value <> "" And Me.TextBox35.Value <> "" And _

            Me.TextBox36.Value <> "" Then

            CommandButton5.Visible = False

            CommandButton19.Visible = False

            CommandButton20.Visible = True

    End If

End Sub
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Hi
try changing line shown in BOLD to Exit Do & see if resolves your issue.

Rich (BB code): 
              Me.TextBox34.Value = sh.Range("AW" & f.Row).Value

                Me.TextBox35.Value = sh.Range("AX" & f.Row).Value

                Me.TextBox36.Value = sh.Range("AN" & f.Row).Value
Exit Do

              End If

              Set f = r.FindNext(f)

            Loop While f.Address <> cell

          End If

Dave
 
Upvote 1
Solution
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA code not finding value in Textbox1.
Replies
3
Views
445
dmt32
D
J
  • Solved
Code not working
Replies
3
Views
493
dmt32
D
J
  • Question Question
Update row based on textbox values
Replies
2
Views
471
Jayliam
J
H
  • Question Question
Error when double click on listbox1 to populate date on to boxes
2
Replies
14
Views
2K
hsolanki
H
Y
  • Question Question
Userform to Update or delete information from cells in an Excel spreadsheet
Replies
4
Views
439
yannine
Y

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top