Workbook Open

[sassriverrat]

So I have some code here that is beating my in the head.
The code is supposed to open up the workbook, immediately making it not visible. Then, it should go, check the registry. If the user has not made a "name" before, it should show Userform17. Userform17 has a single input box (Userform17.TextBox1.Value) that should become the "name" for the registry. This name should also populate cell B34:F34 and it should throw today's date into cell C36. If the program is already "Registered," then it should skip ahead and see if the date in cell E37 is greater than or equal to today. If it is not, it should kick out with an Inputbox that requests a "registration key" which is just a string of numbers in cell B39. If the correct string is put in, then cell C36 should be given today's date. Now if the program is registered, the date in cell E37 is greater than or equal to today, the the program should show one of three userforms (1, 2, or 3) dependent on the name of the workbook.

However, currently the program is showing Userform17 every time it opens, it not populating B34 nor "registering" the program, and is not showing the appropriate userform after all of this. Ideas? Thanks for taking a look!

Private Sub Workbook_Open()

'This will show the Userform 1 when starting and hide the excel sheet in the background
'Also checks to see if program has ever been run, and if not, loads program for the first time
Application.Visible = False
Dim s As String
Dim edate As String
Dim namer As String
Dim d As String


d = Sheets("Developer").Range("B39")          'registration key
edate = Sheets("Developer").Range("E37")      'expiration date
namer = Sheets("Notes").Range("N4")           'just a name


s = GetSetting("DemoTest", "Registration", "Username")
If s = "" Then
        s = InputBox("Please enter your name:", name) 'I really don't want the inputbox. I really want the userform
        'UserForm17.Show
        
        If s <> "" Then
            's = UserForm17.TextBox1.Value
            Sheets("Developer").Range("B34") = s
            SaveSetting "DemoTest", "Registration", "Username", s
            Sheets("Notes").Visible = xlSheetVisible
            Sheets("Notes").Select
            Sheets("Developer").Range("C36") = Today
            'If s <> "" Then MsgBox "Welcome to the " & name & " Voyage Reporting System." & vbCrLf & "Please input the appropriate data to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", vbOKOnly, name
            Application.Visible = True
        End If


Else:
    If ExpirationDate = edate Then
        If Now() < ExpirationDate Then
            If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
                UserForm1.Show
            ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
                UserForm2.Show
            Else: UserForm3.Show
            End If
        Else: d = Application.InputBox("Your workbook date has expired. Please enter the registration key to renew your license.", namer)
            If d = CStr(Worksheets("Developer").Range("B39").Value) Then
                    Sheets("Developer").Range("C36") = Today
                    MsgBox "Welcome Back", vbOKOnly, namer
            End If
        End If
    End If
        
        
End If
Application.Visible = True   'inserted just to check workbook
'Protects/Hides sheets on startup
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.name = "Notes" Then
            sh.Protect Password:=Worksheets("Developer").Range("B17:E17").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Ports" Then
            sh.Protect Password:=Worksheets("Developer").Range("B19:E19").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Developer" Then
            sh.Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            End If
    Next sh
    
End Sub

 

[Logit]

.
You are delving into an area (Registry) that I have not experimented with yet. One thing
I have noticed here is sometimes placing all of the code into ThisWorkbook module can cause
issues. Now this may not occur with others and sometimes it does not occur here depending on
what my code is attempting to do.

When that occurs here, I make a call to the macro in the WorkBook_Open. Placing the actual
code in a REGULAR module with another macro name.

Hopefully this will assist you partially with some of your issues ?

 

[sassriverrat]

So I'm 99% positive my error lies with how I'm getting s. I've tried a few different approaches and none work. See below.

each of these is giving me teh same 1004 error "Application-defined or object-defined error"

 Dim s as string
If s = "" Then

'works 
s = inputbox("Please enter your name:") 

'doesn't work
s = sheets("Developer").Range("B34")
s = sheets("Developer").Range("B34").value
s = Userform17.Textbox1
s = Userform17.Texbox1.Value

'also doesn't work putting any of these after the next line of code
If s <> "" Then SaveSetting "DemoTest", "Registration", "Username", s
sheets("Developer").Range("B34") = s
sheets("Developer").Range("B34").Value = s

 

[Logit]

.
The error message you are receiving relates to how you have DIM'd the variable s.

Dim s as string                    
If s = "" Then                    
                        
'You've dim'd S as a string
'There may be additional code you are not showing but
'you have opened an IF statement and not closed it with
'END IF. That must occur at some point in the code

'works 
s = inputbox("Please enter your name:")     


'Here you are using the variable s as a VARIANT but you've 
'not DIM'd it as such. It is best to use different variables
'for different purposes. Excel can't tell the difference if                         
'you use the same variable for everything.


'doesn't work
s = sheets("Developer").Range("B34")        
'Here you are using s as a variable for the range B34.


s = sheets("Developer").Range("B34").value    
'Now you are using s as a variable for a VALUE (or string)


s = Userform17.Textbox1                
'Here s is being used as a variable for an OBJECT


s = Userform17.Texbox1.Value            
'Now s is being used as a variable for a VALUE (or string)


'also doesn't work putting any of these after the next line of code
If s <> "" Then SaveSetting "DemoTest", "Registration", "Username", s
sheets("Developer").Range("B34") = s
sheets("Developer").Range("B34").Value = s

 

[sassriverrat]

So yes, there is more code to this. However, it I use the s = inputbox piece then it works perfectly and runs through. I did find that part of the problem with the "B34" piece was that it was in the wrong order of code for whole thing. So now my piece is working using an input box. I will revert to the other post about the userform integration.

As far as the registry, I've used the registry to recognize when the program is new to the computer (user) and and then I used a hidden excel sheet with dates and increments to set the "license key expiration" as I call it, thus allowing me to associate a license key to the sheet and make it inoperable to the average user should today's computer date exceed that of the one on the hidden sheet.

 

[sassriverrat]

well- @Logit I've posted the full code below. It's now working as intended (minus wanting the userform17 to work instead of the inputbox. Hopefully this helps anyone else that has questions about it.

Private Sub Workbook_Open()

'This will show the Userform 1 when starting and hide the excel sheet in the background
'Also checks to see if program has ever been run, and if not, loads program for the first time
Application.Visible = False
Dim s As String
Dim edate As String
Dim namer As String
Dim d As String
Dim ExpirationDate As String


ExpirationDate = edate
d = Sheets("Developer").Range("B39")          'registration key
edate = Sheets("Developer").Range("E37")      'expiration date
namer = Sheets("Notes").Range("N4")           'just a name


s = GetSetting("DemoTest", "Registration", "Username")
If s = "" Then
        Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
        Sheets("Developer").Range("B34:F34").ClearContents
        s = InputBox("Welcome to the " & namer & " Voyage Reporting System." & vbCrLf & "Please input the appropriate name to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", namer, "Bridge")
        'UserForm17.Show
        's = UserForm17.TextBox1
        
        If s <> "" Then
            's = UserForm17.TextBox1.Value
            Sheets("Developer").Range("B34") = s
            SaveSetting "DemoTest", "Registration", "Username", s
            Sheets("Notes").Visible = xlSheetVisible
            Sheets("Notes").Select
            Sheets("Developer").Unprotect Password:=Worksheets("Developer").Range("B15:E15").Value
            Sheets("Developer").Range("C36") = Date
            'If s <> "" Then MsgBox "Welcome to the " & name & " Voyage Reporting System." & vbCrLf & "Please input the appropriate data to initialize the system for the first time." & vbCrLf & vbCrLf & "Note: this information can be modified later by clicking on the [Developer] button.", vbOKOnly, name
            Application.Visible = True
        End If


Else:
    'If ExpirationDate > edate Then
        If ExpirationDate < Date Then
            If ActiveWorkbook.name = "Master Voyage Report.xlsm" Then
                UserForm1.Show
            ElseIf ActiveWorkbook.name = "Current Voyage Report.xlsm" Then
                UserForm2.Show
            Else: UserForm3.Show
            End If
        Else: d = Application.InputBox("Your workbook date has expired. Please enter the registration key to renew your license.", namer)
            If d = CStr(Worksheets("Developer").Range("B39").Value) Then
                    Sheets("Developer").Range("C36") = Date
                    MsgBox "Welcome Back " & s, vbOKOnly, namer
            End If
        End If
    'End If
        
        
End If
Application.Visible = True   'inserted just to check workbook
'Protects/Hides sheets on startup
Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.name = "Notes" Then
            sh.Protect Password:=Worksheets("Developer").Range("B17:E17").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Ports" Then
            sh.Protect Password:=Worksheets("Developer").Range("B19:E19").Value, UserInterfaceOnly:=True
            End If
        If sh.name = "Developer" Then
            sh.Protect Password:=Worksheets("Developer").Range("B15:E15").Value, UserInterfaceOnly:=True
            End If
    Next sh
    
End Sub

 

[Logit]

.
Glad you have it functioning. Great job !

Cheers.