calling a userform and reverting back

S

sassriverrat

Well-known Member
Joined
Oct 4, 2018
Messages
655
I have a section of code below from a much larger piece. The section of code worked when using an input box to acquire a userinput. However, I'd like to have userform replace the inputbox. I've tried just using a userform and inserting the line (as seen below) but that didn't work. I need the rest of the code below to call to the userform, allow the user to input data into textbox1, and then return to the lines of code being run.

thanks for the help

Code: 
dim s as string

If s = "" then
'this works
s = inputbox("Please input here",vbokonly)

'I wish this worked instead
call userform17
s = userform17.textbox1.value
unload userform17

'rest of this section of working code
If s <> "" then 
blah blah blah
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
instead of line call userform17

Code: 
UserForm17.Show
 
Last edited:
Upvote 0
What code do you have in userform17 to close/hide the form?
 
Upvote 0
Like this:
Userform: myInputBox / Userform17
Rich (BB code): 
Public Property Let Message(ByVal msg As String)
    Me.Label1.Caption = msg
End Property

Public Property Get Value() As String
    Value = Me.TextBox1.Value
End Property

Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = 1
    Me.Hide
End Sub

Calling function:
Rich (BB code): 
Public Function cInputBox(ByVal message As String) As String

    With New MyInputBox
        .Message = message
        .Show
        cInputBox = .Value
    End With
    
End Function


Your sub:
Rich (BB code): 
Sub test()
Dim s As String

If s = "" Then
    s = cInputBox("Enter your text here...")
    MsgBox s
End If

End Sub
 
Last edited:
Upvote 0
@Yongle and @Norie

Appreciate the posting. I have no problem pulling up userform17, whether it be via .show or calling it. and to hide it, I have been using "unload me". My issue lies with getting the textbox of the userform to replace the inputbox of my old code. I used an input box before to feed what s was to equal. I tried playing with getting the s to equal the value of the textbox but it would never actually input.
@Kyle123 Thank you! I'll give that a shot and get back to you tonight
 
Upvote 0
@Kyle123
Ok going back and reading this now- what exactly or how exactly are you proposing I insert this into my work?
Mainly, I wonder because the part that I want to replace (code below), is in the workbook_open portion of the workbook.

Note- I wish to replace the inputbox below with userform17 and the code below is working (for using an inputbox).

Code: 
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
 
Upvote 0
Yes, I understand what you are trying to do. My approach was fairly generic, but if you want it really simple then try this:
Code: 
Public Function cInputBox() As String

    With New Userform17
        cInputBox = .Textbox1.Value
    End With
    
End Function

In your code you need to change:
's = UserForm17.TextBox1

To:
s = cInputBox()

In userform17, you need to prevent it unloading itself, so you need to add this:
Code: 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = 1
    Me.Hide
End Sub

In your OK button, or Submit or whatever you've called it in Userform17 instead of having something like:

Unload Me

To close the form, change it to:

Me.Hide
 
Upvote 0
Oh duh. That makes sense. I was having a brain fart looking at your previous piece but I glossed over the Public line there and it threw me off. this should work perfectly. I'll plug it in! Thanks
 
Upvote 0
Hmmm. So no go. It's back to not registering s in the registry... but my inputbox continues to work if I re-enable it (i.e. just remove the apostrophe). So I'd say the function here isn't working.
 
Upvote 0
cInputBox disabled in this code but you can see how I had it setup.

Code: 
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 = cInputBox()
        
        If s <> "" Then
            's = cInputBox()
            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
Public Function cInputBox() As String
With New UserForm17
    cInputBox = .TextBox1.Value
End With
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Clicking on a Userform label puts its label name automatically in a textbox
Replies
3
Views
586
chazrab
C
M
  • Solved
How to update worksheet table data using userform listbox
Replies
3
Views
638
dmt32
D
C
  • Question
Generic Code To Highlight And Copy Any Selection Of Text On Any Textbox On Any Userform In An Application
Replies
0
Views
444
chazrab
C
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
JohnGow383
  • Question
A question about a Userform - Pressing Enter to Input Value
Replies
1
Views
714
JohnGow383
JohnGow383

Forum statistics

Threads
1,214,991
Messages
6,122,628
Members
449,095
Latest member
bsb1122

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top