calling a userform and reverting back

[sassriverrat]

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

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

 

[Kyle123]

You’ve commented it out

 

[Kyle123]

This should also be:

Public Function cInputBox() As String
With New UserForm17
    .Show
    cInputBox = .TextBox1.Value
End With
End Function

Note the .Show.

If it's still not working, post the code from your user form

 

[sassriverrat]

sorry things got really busy. Anyway, here's the userform code. and I had it commented out just so the current code would work.

  Private Sub CommandButton1_Click()Me.Hide
Sheets("Notes").Visible = True
Sheets("Notes").Select
Application.Visible = True
End Sub




Private Sub TextBox1_Change()
Sheets("Developer").Range("B34").Value = TextBox1.Value
cInputBox = TextBox1.Value


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

 

[Kyle123]

Your user form code is nothing like the code I posted.

If you want a working example, download the workbook from here:https://www.excelforum.com/tips-and-tutorials/1274147-masked-inputbox-inputbox-for-passwords.html

 

[sassriverrat]

I'm terribly confused where you wanted everything then.....

 

[Kyle123]

My code is very explicit, please read it properly. Have you downloaded the workbook?

 

[sassriverrat]

Ok. downloaded the workbook. Seems like there's a lot more going on there. Further, doesn't seem that the workbook actually "sets" the input to go anywhere....just the msgbox that pops up with the password. interesting idea

 

[Kyle123]

The principle is identical to my first post in this thread. I don't really know what this means:

Further, doesn't seem that the workbook actually "sets" the input to go anywhere

It sets it to the value property of the userform when the OK button is pressed, this is then read by the calling function and returned to the sub

 

[sassriverrat]

also, here's the coding I went with- so maybe I missed something?

Userform17 itself

Private Sub CommandButton1_Click()Me.Hide
Application.Visible = True
End Sub
Public Property Let Message(ByVal msg As String)
    Me.Labell.Caption = msg
End Property
Public Property Get Value() As String
    Value = Me.TextBox1.Value
End Property




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

Within the thisWorkbook piece

Public Function cInputBox() As String
With New UserForm17
    .Message = Message
    .Show
    cInputBox = .Value
End With
End Function

AND

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()
        MsgBox s
        
        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

 

[Kyle123]

You can simplify to the below:

Userform17

Private Sub CommandButton1_Click()
    Me.Hide
    Application.Visible = True
End Sub

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




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

Calling function:

Public Function cInputBox() As String
    With New UserForm17
        .Show
        cInputBox = .Value
    End With
End Function