VBA Hide rows based on button selection

[anand3dinesh]

Hi Please some one help me to find the solution for the below question?

I have 2 sheets called Buttons and Database as shown in the below image.
Upon button selection I would like to show only selected Name/Skills in Database sheet.

eg: If "Dinesh" button selected show only Dinesh rows in Database
If "Excel" Button selected then i would like to show only Excel rows in Database
IF "Dinesh" and "Excel" both button selected then I would like to show only Dinesh and Excel in Database.

I hope my question is clear.
Thanks in advance

 

[tj4242]

I am not sure this is what you are after but here is a shot. Name is in column A on sheet Database. Skills are in Column B.

Also, wouldn't this be easier without the buttons or VBA and instead just use a filter?

Sub Button1_Click()

    ' Dinesh Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row           'find the last row

    For i = 2 To numRows                                                'loop all rows
        If Sheets("Database").Cells(i, 1) = "Dinesh" Then               'if this is the person we are looking for
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack          'show their name
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack          'show their skill
        Else                                                            'not our person
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite         'Hide their name
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite         'hide their skill
        End If
    Next
End Sub
Sub Button2_Click()

    ' Harry Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows
        If Sheets("Database").Cells(i, 1) = "Harry" Then
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
        End If
    Next
End Sub
Sub Button3_Click()
    ' Excel Button
    
    Dim numRows As Long
    
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows

        If Sheets("Database").Cells(i, 2) = "Excel" And Sheets("Database").Cells(i, 1).Font.Color = vbBlack Then
            'if this is the skill and the persons name is visible
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
        End If
        
    Next
End Sub

 

[anand3dinesh]

I am not sure this is what you are after but here is a shot. Name is in column A on sheet Database. Skills are in Column B.

Also, wouldn't this be easier without the buttons or VBA and instead just use a filter?

Sub Button1_Click()

    ' Dinesh Button
   
    Dim numRows As Long
   
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row           'find the last row

    For i = 2 To numRows                                                'loop all rows
        If Sheets("Database").Cells(i, 1) = "Dinesh" Then               'if this is the person we are looking for
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack          'show their name
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack          'show their skill
        Else                                                            'not our person
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite         'Hide their name
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite         'hide their skill
        End If
    Next
End Sub
Sub Button2_Click()

    ' Harry Button
   
    Dim numRows As Long
   
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows
        If Sheets("Database").Cells(i, 1) = "Harry" Then
           Sheets("Database").Cells(i, 1).Font.Color = vbBlack
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
        End If
    Next
End Sub
Sub Button3_Click()
    ' Excel Button
   
    Dim numRows As Long
   
    numRows = Sheets("Database").Range("A:A").End(xlDown).Row

    For i = 2 To numRows

        If Sheets("Database").Cells(i, 2) = "Excel" And Sheets("Database").Cells(i, 1).Font.Color = vbBlack Then
            'if this is the skill and the persons name is visible
           Sheets("Database").Cells(i, 2).Font.Color = vbBlack
        Else
            Sheets("Database").Cells(i, 2).Font.Color = vbWhite
            Sheets("Database").Cells(i, 1).Font.Color = vbWhite
        End If
       
    Next
End Sub

Thanks for response but, It works only when one button is clicked. i don't think it works when both Name and Skill selected?

 

[tj4242]

It works for me. It obviously needs more work because there are only 3 buttons implemented.

The Dinesh button call Button1_Click(). The Harry button calls Button2_Click() and the Excel button calls Button3_Click()

When I click the Excel button I get

When click the Dinesh button

When I click the Dinesh and then the Excel I get

 

[anand3dinesh]

It works for me. It obviously needs more work because there are only 3 buttons implemented.
View attachment 81327
The Dinesh button call Button1_Click(). The Harry button calls Button2_Click() and the Excel button calls Button3_Click()

When I click the Excel button I get
View attachment 81328

When click the Dinesh button
View attachment 81329

When I click the Dinesh and then the Excel I get
View attachment 81330

I figured it out, it requires only 2 subs to do that one sub for Name col and one to Skills Col.
I want to hide the entire row not just match colour property as background.

i used autofilter method and it worked.

 

[anand3dinesh]

It works for me. It obviously needs more work because there are only 3 buttons implemented.
View attachment 81327
The Dinesh button call Button1_Click(). The Harry button calls Button2_Click() and the Excel button calls Button3_Click()

When I click the Excel button I get
View attachment 81328

When click the Dinesh button
View attachment 81329

When I click the Dinesh and then the Excel I get
View attachment 81330

Thanks for your efforts