View Full Version : Solved: Userform Restart Sub
RECrerar
08-08-2007, 06:37 AM
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
08-08-2007, 06:49 AM
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
08-08-2007, 07:20 AM
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
08-08-2007, 07:37 AM
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
08-08-2007, 07:39 AM
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
08-08-2007, 07:53 AM
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
08-08-2007, 08:04 AM
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
08-08-2007, 08:07 AM
There actually isn't any need for public variables - see what I posted.
RECrerar
08-08-2007, 08:22 AM
Okay
Is there anything wrong with using public variables?
Norie
08-08-2007, 09:29 AM
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
08-08-2007, 09:31 AM
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
08-09-2007, 01:10 AM
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
08-09-2007, 01:19 AM
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
08-09-2007, 01:39 AM
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
08-09-2007, 02:04 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.