Help with "If" and "ElseIf" statements

[kbishop94]

trying to get a commandbutton working where it toggles between hiding columns that a cell in each either contains an "R" or and "E". When the user clicks on the button it hides those columns and changes the caption to indicate what is currently being viewed.

THIS simple code works that I use in a different workbook:

Private Sub cmdShowHide_Click()


If cmdShowHide.Caption = "Hide Hidden Columns" Then
  ActiveWorkbook.Worksheets("Seatex Incident Log").Range("C1,E1,O1").EntireColumn.Hidden = True
  
  cmdShowHide.Caption = "Show Columns"


Else
  ActiveWorkbook.Worksheets("Seatex Incident Log").Range("C1,E1,O1").EntireColumn.Hidden = False


  cmdShowHide.Caption = "Hide Hidden Columns"
  
End If


End Sub

The new code that I have been trying to make work takes that code a step further where instead of hiding specific columns/rows, the new one will look to see if the column contains a certain character ("R" or an "E") and hides only those rows... here is the code that i have (NOT working)

Private Sub CommandButton30_Click()


    ' button for EL CAMPO EMPLOYEES(NEW)
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    
If CommandButton30.Caption = "EL-CAMPO" Then


For Each Cell In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)
If Cell = "E" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True
Next


CommandButton30.Caption = "ROSENBERG"


ElseIf CommandButton30.Caption = "ROSENBERG" Then


For Each Cell In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)
If Cell = "R" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


End If


End Sub

Ultimately I need to make this (somehow) a 3-way switch (hence why I am trying to get it to work with ElseIf) because I also need it to toggle not only columns with "R" or "E", but also ones with "R" and "E" (but not in the same cell... separate cells that contain either "R" or "E")
I was hoping to get it working with 2 options first before exanding it to include the 3 different options.

Thanks for any assistance anyone can throw my way.

 

[kbishop94]

Picture to better illustrate what I am after:

Row 5 contains either an R or an E (to specify a location of where the employee works)

Here it is unsorted containing both R's and E's:

And after hitting the commandbutton "toggle between employees" (should say 'facility'... i was in a hurry and mis-typed that) it drills down to first E columns, hitting the button again changes the caption and shows only R columns, and (dont know if this is possible), the third time goes to showing R's and E's together and changes the caption to ALL. I have single parts of all that code working... but I cannot get the code that does all of that correctly so that it works as one single piece of code when executed. Thanks again for any help

example showing only E's:

 

[wideboydixon]

Perhaps you could try removing ".SpecialCells(xlCellTypeVisible)"

WBD

 

[kbishop94]

Thank you for your reply.

The specialcells part of the code is there because prior to this button being clicked, there already has been 1 script ran... the buttons in the top left corner represent the departments where employees work... it goes like this:

1. all employees are shown across the top in row one.
2. user selects one of the command buttons in the top left corner which drills down to only the employees working in that department (in the picture I posted above only the employees working in the "packaging" department are represented.)
3. Using the 'specialcells' command, i now need it to look at just the employees working in the packaging department (or whatever department has been chosen) and then further narrow it down to only those working in 1 facility (that row is row 5 with the "E"s and the "R"s....)
Like i mentioned, I have a button working that when selected works fine by narrowing it down to just one facility ("E" in this case), but i have been trying to expand that code to go one step further and toggle between the 2 different facilities (the working code for drilling down to only the employees with an "E" is shown in my first post in the first CODE box.)

THanks again for commenting. I appreciate it wideboydixon.

 

[wideboydixon]

The problem is that after hiding the cells because they don't match the right location, you're then not considering them next time you run the macro so you'll end up with everything hidden I'm guessing.

WBD

 

[kbishop94]

Correction; the first bit of code I posted in my first reply is code that works as a 'toggle' button in another workbook.

HERE is the code I have that WORKS for hiding columns so that it shows only those employees with an "E" in row 5. (what i need is this expanded so that it toggles back and forth between showing "E" 's and "R" 's that are in Row 5:

Private Sub CommandButton30_Click()

[COLOR=#008000]    ' button for EL CAMPO EMPLOYEES[/COLOR]
    
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

Dim lCol As Long
    lCol = ActiveSheet.UsedRange.Columns.Count
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(5, 6), Cells(5, lCol)).SpecialCells(xlCellTypeVisible)

If Cell = "E" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True

Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

When that command is ran it gives you what is represented in my 2nd picture in my 2nd post. And to better answer your quesetion regarding SpecialCells, without the SpecialCells in that code it would 'unfilter' what was previously selected (in the example picture shown above the "packaging' button was selected) and then it would go back to showing all departments, then it would select only the "E"s. (not what i want) But by including the SpecialCells in the code, it then retains the previous "Packaging" commandbutton that was selected and further drills down to only show the employees with an "E" in row 5. Thank you

 

[wideboydixon]

Yep but see my comment above. Those "R" cells are now hidden and therefore not considered by your loop.

WBD

 

[kbishop94]

I dont understand VBA all that well (at least compared to 99.9% of people on this board... I'm a self taught novice...., at best(!) lol) but here is a short screen grab video showing what it does (using the component that does work; the part where it shows only one faclity ("E") after selecting a department button:
1. starts by showing ALL employees in row 1 (both facilities ("E" and "R"s) and all departments.
2. selecting the command button for "packaging", (in A1) which then selects any employee (column) with a "Pr" listed in row 3 (I used the "like" command with "*Pr*" as the string its looking for as there are up to 19 different departments that could be found in row 3)
3. After a department is narrowed down and displayed, the command button for "El Campo Employees" is selected at which it further drills down to select only those shown on the screen containing an "E" in row 5. (I could go the easy route and just make another button for the "R" employees, but it would run snazzier being a toggle button to go back and forth between "E"s and "R"s lol)


24 second video of the spreadsheet showing what I described:

https://www.youtube.com/watch?v=X49HDPfgwzQ&feature=youtu.be

 

[kbishop94]

FWIW, here is the code for how it selects only the Rows and the COlumns that contain "*Pr*" (or any other 19 departments depending on which button in A1 is selected) and displays and formats accordingly: This is the code for the Packaging commandbutton that is executed in the video above.

For Each Cell In Range(Cells(3, 6), Cells(3, lCol))


If Cell Like "*Pr*" Then Columns(Cell.Column).Hidden = False Else Columns(Cell.Column).Hidden = True


Next
    For Each Cell In Range(Cells(6, 1), Cells(rCol, 1))
        With Range(Cells(Cell.Row, 1), Cells(Cell.Row, lCol))
            If Cell Like "*Pr*" Then
                .Interior.Color = 65280
                .Font.Bold = True
                .Borders.Weight = xlMedium
            Else
                .Font.Color = 10213316
                Rows(Cell.Row).RowHeight = 14
                .Borders.Weight = xlHairline
            End If
        End With
Next

Column A (which is hidden in the screen shots/video) also contains the department abbreviations (like "Pr" for packaging) for each equivalent SOP (procedure) that is listed in column A. That is how the code is formatting/hiding/unhiding the correct employee (row 1) and SOP (column A) which is executed when the commandbutton is selected.

 

[kbishop94]

Yep but see my comment above. Those "R" cells are now hidden and therefore not considered by your loop.

WBD

Oh, I see now what you meant... (finally!) (today still feels like a Monday to me ... sorry for not seeing what you meant.) I am just going to have to do it with 2 buttons. Thank you, WBD.