Open 1 UserForm then open another UserForm

[dharv3y78]

HI,

I have a VBA UserForm that is a Licence Key that shows if it is the first time it is used on a computer then shows another Userform to Login to the Spreadsheet but the issue that i have is if the licence key has previously been entered it will not show the second userform and i need the second userform to always show and the Licence Key Userform to only show if it has not previously been installed on the computer before. Any Help you can provide would be appreciated.

The UserForms that i have are
UserForm1
Login

The VBA Codes i have are

This Is for the Licence Key (UserForm1)

Private Sub Label6_Click()
LKey = "987654321"
Sheet14.Range("A1") = Sheet14.Range("A1") + 1
If TextBox1 & TextBox2 & TextBox3 & TextBox4 & TextBox5 & TextBox6 & TextBox7 & TextBox8 & TextBox9 = LKey Then
    MsgBox "SUCCESSFUL", vbInformation, ""
    'Application.Visible = True
    ThisWorkbook.Save
    Unload Me
    Login.Show
Else
    If Sheet14.Range("A1") = 2 Then
        MsgBox "INVALID KEY ENTERED TWICE - THIS FILE WILL NOW CLOSE", vbInformation, ""
        Sheet14.Range("A2") = ""
        If Application.Workbooks.Count > 1 Then
            ThisWorkbook.Close
        Else
            ThisWorkbook.Save
            Application.Quit
        End If
    Else
        MsgBox "INVALID KEY", vbInformation, ""
        Unload Me
        UserForm1.Show
    End If
End If
End Sub

Then the Macro Code that i have that is used to open the Licence Key UserForm if needed.

Option Explicit
Sub Auto_Open()
    'Application.Visible = True
With Sheet14
    .Visible = xlSheetVeryHidden
    If .Range("A2") = "" Then
        .Range("A1") = ""
        .Range("A2") = Environ$("ComputerName")
        Application.Visible = False
        UserForm1.Show
    ElseIf .Range("A2") <> Environ$("Computername") Then
        MsgBox "THIS FILE IS NOT LICENCED FOR THIS PC", vbInformation, ""
        If Application.Workbooks.Count > 1 Then
            Application.DisplayAlerts = False
            ThisWorkbook.Close
            Application.DisplayAlerts = True
        Else
            ThisWorkbook.Save
            Application.Quit
        End If
    End If
End With
End Sub

Thanks

 

[myall_blues]

Showing your login form (Login.Show) is embedded in the license key form, and is therefore conditional on that form being displayed.

A better way to do it would be to put some checks up front in your Auto_Open code (which I would recommend changing to a Workbook_Open event - for a good explanation why see the answer here) and proceed something like this:

1. At the beginning of the Workbook_Open event, check if Sheet14 exists (and optionally contains a flag value).
2. If Sheet14 does not exist show LicenceKey - if LicenceKey runs successfully create Sheet14 (and optionally add a flag value to it). If LicenceKey didn't run successfully kill the macro.
3. Unless killed, execution will return to the Workbook_Open event at this point.
4. Alternatively if Sheet14 already exists at step 2 just carry on with code - don't show LicenceKey
5. Unconditionally show Login.

Let me know if you need some example code.

 

[dharv3y78]

Showing your login form (Login.Show) is embedded in the licence key form, and is therefore conditional on that form being displayed.

A better way to do it would be to put some checks up front in your Auto._Open code (which I would recommend changing to a Workbook_Open event - for a good explanation why see the answer here) and proceed something like this:

1. At the beginning of the Workbook_Open event, check if Sheet14 exists (and optionally contains a flag value).
2. If Sheet14 does not exist show LicenceKey - if LicenceKey runs successfully create Sheet14 (and optionally add a flag value to it). If LicenceKey didn't run successfully kill the macro.
3. Unless killed, execution will return to the Workbook_Open event at this point.
4. Alternatively if Sheet14 already exists at step 2 just carry on with code - don't show LicenceKey
5. Unconditionally show Login.

Let me know if you need some example code.

Hi Myall_blues,

Thanks for this if you are able to provide a sample code that would achieve what i am after it would be appreciated. I already have sheet14 ready for the code to run in. I'm just not sure how to layout the code to put under Workbook_Open, so if you could help it will be greatly appreciated.

Thanks

 

[myall_blues]

Somewhere toward the end of the code for the LicenceKey (the form code for Userform1) you should add some code to create a hidden sheet in the workbook. Preferably give it an unlikely name that users won't use if they add a sheet themselves. So in the confirming _Click event for the form (I think you have it as Label6_click) check that everything is OK and then add

        If Not CheckSheet("MacroData_Hidden") Then
            AddMacroDataSheet
        End If

The code for those two functions is:

Public Function CheckSheet(ByVal sSheetName As String) As Boolean
    '
    ' Check required worksheet exists
    '
    Dim bReturn As Boolean
    Dim i As Integer
    For i = 1 To Sheets.Count
        If Sheets(i).Name = sSheetName Then
            bReturn = True
            Exit For
        End If
    Next i
    CheckSheet = bReturn
End Function

Public Sub AddMacroDataSheet()
    Dim wksNewSheet As Excel.Worksheet
    Set wksNewSheet = Worksheets.Add(Before:=Sheets(1))
    With wksNewSheet
        .Name = "MacroData_Hidden"
        .Visible = xlSheetVeryHidden
    End With
End Sub

Then all you need to do is check if that hidden sheet exists when the Workbook_Open event runs, and if it doesn't, run the license form, and if it does skip running the license form and go straight to the login form.

Private Sub Workbook_Open()
    '
    ' Forms demonstration
    '
    Dim MsgText As String
    '
    ' Set error trap
    '
    On Error GoTo ErrorHandler
    '
    ' Check if licensing routine has previously been run.
    '
    If Not CheckSheet("MacroData_Hidden") Then
        '
        ' Licensing routine not previously run - run it now.
        '
        MsgText = "Licensing form runs here"
        MsgBox MsgText, vbExclamation, "Licensing required"
        '
        ' Remove comments here to open and run the licensing form
        '
        ' Dim frm as New UserForm1
        ' With frm
        '   .Show
        '   Add any other required code
        '   Unload frm
        '   Set frm=Nothing
        ' End With
    End If
    '
    ' Now just run the login form
    '
    MsgText = "Login form runs here"
    MsgBox MsgText, vbExclamation, "Login required"
    '
    ' Remove comments here to open and run the login form
    '
    ' Dim frm as New LoginForm
    ' With frm
    '   .Show
    '   Add any other required code
    '   Unload frm
    '   Set frm=Nothing
    ' End With
    Exit Sub
ErrorHandler:
    MsgBox "An error occurred." & vbCrLf & Err.Number & ": " & Err.Description
End Sub

 

[dharv3y78]

Somewhere toward the end of the code for the LicenceKey (the form code for Userform1) you should add some code to create a hidden sheet in the workbook. Preferably give it an unlikely name that users won't use if they add a sheet themselves. So in the confirming _Click event for the form (I think you have it as Label6_click) check that everything is OK and then add

        If Not CheckSheet("MacroData_Hidden") Then
            AddMacroDataSheet
        End If

The code for those two functions is:

Public Function CheckSheet(ByVal sSheetName As String) As Boolean
    '
    ' Check required worksheet exists
    '
    Dim bReturn As Boolean
    Dim i As Integer
    For i = 1 To Sheets.Count
        If Sheets(i).Name = sSheetName Then
            bReturn = True
            Exit For
        End If
    Next i
    CheckSheet = bReturn
End Function

Public Sub AddMacroDataSheet()
    Dim wksNewSheet As Excel.Worksheet
    Set wksNewSheet = Worksheets.Add(Before:=Sheets(1))
    With wksNewSheet
        .Name = "MacroData_Hidden"
        .Visible = xlSheetVeryHidden
    End With
End Sub

Then all you need to do is check if that hidden sheet exists when the Workbook_Open event runs, and if it doesn't, run the license form, and if it does skip running the license form and go straight to the login form.

Private Sub Workbook_Open()
    '
    ' Forms demonstration
    '
    Dim MsgText As String
    '
    ' Set error trap
    '
    On Error GoTo ErrorHandler
    '
    ' Check if licensing routine has previously been run.
    '
    If Not CheckSheet("MacroData_Hidden") Then
        '
        ' Licensing routine not previously run - run it now.
        '
        MsgText = "Licensing form runs here"
        MsgBox MsgText, vbExclamation, "Licensing required"
        '
        ' Remove comments here to open and run the licensing form
        '
        ' Dim frm as New UserForm1
        ' With frm
        '   .Show
        '   Add any other required code
        '   Unload frm
        '   Set frm=Nothing
        ' End With
    End If
    '
    ' Now just run the login form
    '
    MsgText = "Login form runs here"
    MsgBox MsgText, vbExclamation, "Login required"
    '
    ' Remove comments here to open and run the login form
    '
    ' Dim frm as New LoginForm
    ' With frm
    '   .Show
    '   Add any other required code
    '   Unload frm
    '   Set frm=Nothing
    ' End With
    Exit Sub
ErrorHandler:
    MsgBox "An error occurred." & vbCrLf & Err.Number & ": " & Err.Description
End Sub

Hi myall_blues,

I had to do it a slightly different way as i could not get your way to work for some reason but thanks for your assistance in trying to help me.

Thanks
Donald