Activating hidden Sheets using If statement.

[AKOsman1]

I have multiple sheets in my project. At the beginning all the sheets are hidden except the "Welcome" Sheet. To continue the user must click the button which will Unhide the next Sheet, "CheckExpiry", I want him to be in and hide the "Welcome " sheet". To continue from the "CheckExpiry" sheet to the next sheet, the user must click the button which will unhide the next Sheet, "Password", I want him to be in and hide the "CheckExpiry" sheet. (and so on and on)
I have created a macro for each button on each sheet. I want to create a single macro to be assigned to all the "continue" buttons on all the sheets using the
"IF..IFELSE..END IF" statement; e.g.

if ActiveSheet = "Welcome" then
"CheckExpiry" = Unhide
"Welcome" = Hide
ElseIf ActiveSheet = "CheckExpiry" then
"Password" = Unhide
"CheckExpiry = Hide
End If

Ps. I have worked on basic Excel spreadsheets for some time but I am absolutely new to VBA. Kindly pardon me for my ignorance.

 

[AKOsman1]

Private Sub Workbook_Open() Dim wb As Workbook, sht As Worksheet, n As Integer Set wb = ThisWorkbook For n = 2 To wb.Sheets.Count wb.Sheets.Visible = xlSheetHidden Next n End Sub

Where in the Project do Code "Private" Subs

 

[Skyybot]

Workbook_Open method should be placed in ThisWorkbook of your Workbook VB Project.

 

[AKOsman1]

Instead of using ActiveX controls on your sheet, try using form controls instead.
Developer / Insert / Form Controls & select the form button from that section. Then assign the macro to the button.

I changed to a Form Control Button but got the same message. However, for some reason or the other, I have 2 Workbooks in the Project viz; ThisWorkbook & ThisWorkbook1. I moved the code from ThisWorkbook to ThisWorkbook1 and wala!!! It worked like a charm.
Many thanks for your help and prompt response.

 

[kevin9999]

No quite sure how you did it, but well done anyway!

 

[AKOsman1]

My suggested code is not a private sub. From the developer screen (press Alt+F11) select Insert / Module & copy paste the code in the blank section on the right of the screen.

Sorry, This reply was not meant for you.

 

[AKOsman1]

Copy the following Sub into ThisWorkbook:

Sub HideSheets()
Dim wb As Workbook, sht1 As Worksheet, sht2 As Worksheet, sht3 As Worksheet
Set wb = ThisWorkbook: Set sht1 = wb.Sheets(1): Set sht2 = wb.Sheets(2): Set sht3 = wb.Sheets(3)
Select Case ActiveSheet.Name
    Case sht1.Name
        sht2.Visible = xlSheetVisible
        sht1.Visible = xlSheetHidden
        sht2.Activate
    Case sht2.Name
        sht3.Visible = xlSheetVisible
        sht2.Visible = xlSheetHidden
        sht3.Activate
    Case sht3.Name
        sht1.Visible = xlSheetVisible
        sht3.Visible = xlSheetHidden
        sht1.Activate
End Select
End Sub

Then in each Sheet the following for ActiveX Command Buttons:

Private Sub CommandButton1_Click()
 ThisWorkbook.HideSheets
End Sub

Nothing happens. I need you to know that the Project has for some unknown reason duplicated my sheets which renamed my sheets e.g. Sheet1 is renamed to Sheet 11 and a new Sheet1 was created and Sheet2 was renamed Sheet 21 and a new Sheet2 was created and so on. "ThisWorkBook" changed to "ThisWorkBook1" while retaining "ThisWorkBook". I have renamed my Sheets to "Open", "CheckExpiry", "Password", etc. Would this make any difference to the code you have sent me.

 

[AKOsman1]

Nothing happens. I need you to know that the Project has for some unknown reason duplicated my sheets which renamed my sheets e.g. Sheet1 is renamed to Sheet 11 and a new Sheet1 was created and Sheet2 was renamed Sheet 21 and a new Sheet2 was created and so on. "ThisWorkBook" changed to "ThisWorkBook1" while retaining "ThisWorkBook". I have renamed my Sheets to "Open", "CheckExpiry", "Password", etc. Would this make any difference to the code you have sent me.

I am Confused. I closed the Project and Restarted it. Now when I Click the ActiveX button it gives me an Error:
ActiveX component can't create object or return reference to this object (Error 429)

ActiveX component can't create object or return reference to this object (Error 429)​

Creating objects requires that the object's class be registered in the system registry and that any associated dynamic-link libraries (DLL) be available. This error has the following causes and solutions:

• The class isn't registered. For example, the system registry has no mention of the class, or the class is mentioned, but specifies either a file of the wrong type or a file that can't be found.
  If possible, try to start the object's application. If the registry information is out of date or wrong, the application should check the registry and correct the information. If starting the application doesn't fix the problem, rerun the application's setup program.
• A DLL required by the object can't be used, either because it can't be found, or it was found but was corrupted.
  Make sure all associated DLLs are available. For example, the Data Access Object (DAO) requires supporting DLLs that vary among platforms. You may have to rerun the setup program for such an object if that is what is causing this error.
• The object is available on the machine, but it is a licensed Automation object, and can't verify the availability of the license necessary to instantiate it.
  Some objects can be instantiated only after the component finds a license key, which verifies that the object is registered for instantiation on the current machine. When a reference is made to an object through a properly installed type library or object library, the correct key is supplied automatically.
  If the attempt to instantiate is the result of a CreateObject or GetObject call, the object must find the key. In this case, it may search the system registry or look for a special file that it creates when it is installed, for example, one with the extension .lic. If the key can't be found, the object can't be instantiated. If an end user has improperly set up the object's application, inadvertently deleted a necessary file, or changed the system registry, the object may not be able to find its key. If the key can't be found, the object can't be instantiated. In this case, the instantiation may work on the developer's system, but not on the user's system. It may be necessary for the user to reinstall the licensed object.
• You are trying to use the GetObjectfunction to retrieve a reference to class created with Visual Basic.
  GetObject can't be used to obtain a reference to a class created with Visual Basic.
• Access to the object has explicitly been denied.
  For example, you may be trying to access a data object that's currently being used and is locked to prevent deadlock situations. If that's the case, you may be able to access the object at another time.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

 

[AKOsman1]

I suppose I should have mentioned, hide all the sheets except the Welcome sheet before running the code.

Hi Kevin, As i said the code worked well. However, you are using "Sheet1", "Sheet2", etc; Which means the Sheet positions cannot change. How would you achieve the same result using the Sheet Name. "Sheet1"="Welcome", "Sheet2"="CheckExpiry", etc. Then if Sheet positions are changed there will be no effect on the operation of your code.
Thanking you in anticipation.

 

[Skyybot]

Nothing happens. I need you to know that the Project has for some unknown reason duplicated my sheets which renamed my sheets e.g. Sheet1 is renamed to Sheet 11 and a new Sheet1 was created and Sheet2 was renamed Sheet 21 and a new Sheet2 was created and so on. "ThisWorkBook" changed to "ThisWorkBook1" while retaining "ThisWorkBook". I have renamed my Sheets to "Open", "CheckExpiry", "Password", etc. Would this make any difference to the code you have sent me.

Yes. Where the code references wb.Sheets(1), which in your OP was the "Welcome" sheet, would need to be changed to: wb.Sheets("Welcome"), etc.

 

[kevin9999]

How would you achieve the same result using the Sheet Name. "Sheet1"="Welcome", "Sheet2"="CheckExpiry", etc.

Try the following:

Option Explicit
Sub Hide_Unhide()
    Application.ScreenUpdating = False
    Dim i As Long, j As Long, a
    a = Array("Welcome", "CheckExpiry", "Password") '<-- Put sheet names in the ORDER you want them to open
    i = Application.Match(ActiveSheet.Name, a, 0)
    If i = UBound(a) + 1 Then i = 1 Else i = i + 1
    For j = 1 To Worksheets.Count
        Worksheets(j).Visible = True
    Next j
    For j = 1 To Worksheets.Count
        If j <> i Then Worksheets(j).Visible = False
    Next j
    Application.ScreenUpdating = True
End Sub