Solved: Userform Restart Sub

[RECrerar]

I have a userform that is used to collect a username and password, it checks that these are valid and returns a 'Key' which is needed to later in the program.

I call the userform from the main sub using userform.show

When the userform has been populated and ok selected, I want to restart the main sub at the line below userform.show, how do I do this?

Also I need the variables defined by the userform to be available for use in the main sub, I'm sure this is very easy to do but I can not figure out how to get the main sub to recognise the variables from the userform code. How do I need to have them defined for this to work?

I hope this is clear, thanks in advance for any help

[Bob Phillips]

A macro will restart at the line after Userform.SHow when the form is exited, that is the default.

The simplest way to poass variables is to declare public variables in a standard module, and set these in the formm, read them in the main macro.

Userform



Private Sub cmdOK_Click()
mgUser = TextBox1.Text
mgPwd = TextBox2.Text
End Sub


Standard module



Public mgUser As String
Public mgPwd As String

Sub myMacro()

'do stuff

Userform1.Show

MsgBox mgUser
MsgBox mgPwd

'other stuff
End Sub

[Norie]

Why not just hide the userform?

Then control will be returned to the main sub, and you'll still be able to access the values in the controls.

Where you could create variables and unload the form.

[RECrerar]

Thanks:

xld - the unloading works, don't know why it didn't seem to before.
Norie - Sounds like a very interesting idea, I'll say that I haven't done this as I'm quite new to VBA and don't know how to.

So I am still trying to unload the userform then access the input and it still isn't working. Below is a very simple macro in the same form as the one we are working on. The information is not transferred back to the main sub.
' In User Form
Option Explicit
Dim a As String
Dim b As String

Private Sub CommandButton1_Click()
a = TextBox1.Text
b = TextBox2.Text
Unload UserForm1
End Sub

'In module
Sub main()
UserForm1.Show
msg = a & b
MsgBox msg
End Sub

[RECrerar]

Oh sorry missed a bit, the variables a and b are also defined in the main sub. I tried not defining them in the userform but it threw up errors

[Norie]

Try this.

Private Sub CommandButton1_Click()
Me.Hide
End Sub



Sub main()
Dim a As String
Dim b As String
UserForm1.Show
a = UserForm1.TextBox1.Text
b = UserForm1.TextBox2.Text
Unload UserForm1
msg = a & b
MsgBox msg
End Sub

[RECrerar]

Thanks, that is useful to learn.

Have answered my own problem, I had the Variables defined as module only variables rather than public variables, simply changed 'Dim' to 'Public' and all worked perfectly.

[Norie]

There actually isn't any need for public variables - see what I posted.

[RECrerar]

Okay

Is there anything wrong with using public variables?

[Norie]

Not really, but some might say using them is a bad idea as they are 'available' everywhere.

But if they work for you that's the important thing.

I was just demonstrating another method to do what you want.:)

There are more than likely drawbacks in using what I posted.

I can think of one - you have to explicitly reference the user form by name.

[Bob Phillips]

Thanks:

xld - the unloading works, don't know why it didn't seem to before.
Norie - Sounds like a very interesting idea, I'll say that I haven't done this as I'm quite new to VBA and don't know how to.

So I am still trying to unload the userform then access the input and it still isn't working. Below is a very simple macro in the same form as the one we are working on. The information is not transferred back to the main sub.
' In User Form
Option Explicit
Dim a As String
Dim b As String

Private Sub CommandButton1_Click()
a = TextBox1.Text
b = TextBox2.Text
Unload UserForm1
End Sub

'In module
Sub main()
UserForm1.Show
msg = a & b
MsgBox msg
End Sub


If you are goingf to declare the variablkes in the form not the module as I suggested, you still have to declare them as public, and Hide the form not Unload it.

[RECrerar]

I've taken your advice and the variables are only defined in the module. I think I will stick with just using public variables for now as in this particular program there is no problem with them being available everywhere. I have another program where hiding the userform and not using public variables will be preferable so I will make use of the advice.
The question about the use of public variables wasn't meant to sound defensive, in was a genuine enquiry about the use of public variables. Thanks again, Regards, Robyn

[Bob Phillips]

I personally don't like public variables, it is too loose IMO. I tend to have an application class in my apss, and all globals are accessed via properties of that class. But the odd one here and there isn't really a problem. Even in my case wher I have an application class, I have to have a public variable to refernce the class (:-)).

[RECrerar]

Okay you've lost me. What do you mean by application class? I've only been using VBA for a few weeks, but it somehow seems to have become my job description - people keep asking me advice, which is kinda scary cos I'm only just learning myself. If you've got the time to explain in laymans terms it would be appreciated

[Bob Phillips]

I mean just that, a class that keeps all of the application (my application, not the Excel application) variables. As a very simple, simplistic, example, every application should have a name. Now you could just have public constant declaring it



Public Const AppName As String = "myApp"


but there will be lots like this, and you may have particular functions applying to the app, so I create an application class. For the application name, the class would look like



Private Const mmName As String = "myApp"

Public Property Get Name() As String
Name = mmName
End Property


and then you would access within the application code like so



Public App As clsApplication

Public Sub DoStuff()
Set App = New clsApplication

MsgBox App.Name

'do some stuff

Set App = Nothing
End Sub


Of course the class will be much more complex, more properties and more functions, but that is the basic idea.