topic Re: Passing Var's from Userform to Module in VBA Forum

Passing Var's from Userform to Module

Anonymous — Sun, 11 Dec 2005 18:18:41 GMT

How can i pass a variable from my userform to a sub in another module.
I have a userform with a listbox and a command button
The user will select an item in the listbox and then click the command button which will call a sub from another module.

So in my userform i have this......

Private Sub Listbox1_Click
Var1 = 10
Var2 = 11
End Sub

Private sub CommandButton1_Click
Me.Hide
Call DrawItem
Me.Show
End Sub

Now in Module1 i have the sub DrawItem

Public Sub DrawItem ()
Var1 = NumbofItems
Var2 = NumbofPieces
End Sub

So i need to pass Var1 and Var2 from my userform to the sub in module1.

Any Help. Thanks in advance.

Re: Passing Var's from Userform to Module

Anonymous — Sun, 11 Dec 2005 19:02:43 GMT

The userform is a class and is accessable from anywhere while it is loaded so userform.listbox1.value is available to any sub (use the userform name ,me is only available in the class module). Sometimes you make a dummy control (set as invisible) to hold the variable temporarily.

Re: Passing Var's from Userform to Module

Anonymous — Sun, 11 Dec 2005 22:05:26 GMT

Private Sub Listbox1_Click
Var1 = 10
Var2 = 11
End Sub

Private sub CommandButton1_Click
Me.Hide
Call DrawItem (Var1, Var2)
Me.Show
End Sub

Now in Module1 i have the sub DrawItem

Public Sub DrawItem (Var1 as Variant, Var2 as Variant)
Var1 = NumbofItems
Var2 = NumbofPieces
End Sub

Re: Passing Var's from Userform to Module

Anonymous — Sat, 17 Dec 2005 15:16:26 GMT

Hi Guys, Im still having trouble passing variables between my userform and my module. I uploaded my program. Take a look and see where im going wrong. Its not passing the variables RowNumber and ColNumber to the DrawView Sub in module1. Many thanks in advance if you figure out what it wrong.

Re: Passing Var's from Userform to Module

Anonymous — Sat, 17 Dec 2005 21:19:23 GMT

I had a look. It's better if you strip your code down to find the problem (This takes a while)



'showopen = Trim(Ofname.lpstrfile)

    showopen = Left$(Ofname.lpstrfile, InStr(Ofname.lpstrfile, v

bNullChar) - 1)
It seems that trim isn't killing the null char.
'Fix
'.InsertLines .CountOfLines + 1, "Private sub OK_click()"
'Set objworkbook = Nothing
'Set WorkSheet = Nothing
'Set Objexcel = Nothing

The real problem is that you cant use the term userform1
Until it is created. There must be a way to activate it when you have made it but I don't know it.
Here's a lame way



Dim myForms As VBIDE.VBComponent

    Dim YesNo As Boolean

    For Each myForms In VBE.ActiveVBProject.VBComponents

        If myForms.Type = vbext_ct_MSForm Then

            If myForms.Name = "UserForm1" Then

                Set U = UserForms.Add("UserForm1")

                 'UserForm1.Show

                U.Show

                YesNo = True

                Exit For

            End If

        End If

    Next

   

    If Not YesNo Then PromptsCollection

' and later in Public Sub PromptsCollection

ShowForm:

'VBA.UserForms.Add(Promptsform.Name).Show

Set U = UserForms.Add("UserForm1")

U.Show

<\code>

Re: Passing Var's from Userform to Module

Anonymous — Mon, 19 Dec 2005 13:20:11 GMT

Ok so if my problem resides in VBA not activating my userform because it is being created at runtime. What if i just have the userform already loaded into the DVB and all i add at runtime would be the prompts to the multipage. Do you think that would work out fine?
I don't want to move all my code around if you think it wouldn't work.

Re: Passing Var's from Userform to Module

Anonymous — Mon, 19 Dec 2005 15:38:52 GMT

Well what I did, did work and it was late binding. Dim U As Object This makes sense in your situation.
However I think having the form there in the dvb will make it easier.

Re: Passing Var's from Userform to Module

Anonymous — Wed, 21 Dec 2005 03:51:57 GMT

So i created my userform inside my dvb. But now i am having trouble adding my textboxes etc.
I tried this
With Mulitpage1.Pages(0).controls.add("forms.textbox.1", "TextHere" , True)

Also tried this..

Set Mytextbox = Multipage1.add(Promptnamecode, PromptName, 0)
Neither will work. Any Suggestions or i am just gonna go your way. I just though my code could be cleaner if i just created the userform and added the elements.

Re: Passing Var's from Userform to Module

fxcastil — Wed, 21 Dec 2005 13:46:43 GMT

macleodjb

Sub RunTimeButton()
' Adds a button at runtime

' Make sure access to the VBProject is allowed
On Error Resume Next
Set x = ActiveWorkbook.VBProject
If Err <> 0 Then
MsgBox "Your security settings do not allow this macro to run.", vbCritical
On Error GoTo 0
Exit Sub
End If

Dim Butn As CommandButton
Set Butn = UserForm1.Controls.Add("Forms.CommandButton.1")
With Butn
.Caption = "Added at runtime"
.Width = 100
.Top = 10
End With
UserForm1.Show
End Sub

Sub DesignTimeButton()
' Adds a button at design-time

' Make sure access to the VBProject is allowed
On Error Resume Next
Set x = ActiveWorkbook.VBProject
If Err <> 0 Then
MsgBox "Your security settings do not allow this macro to run.", vbCritical
On Error GoTo 0
Exit Sub
End If

Dim Butn As CommandButton
Set Butn = ThisWorkbook.VBProject.VBComponents("UserForm1") _
.Designer.Controls.Add("Forms.CommandButton.1")
With Butn
.Caption = "Added at design-time"
.Width = 120
.Top = 40
End With
VBA.UserForms.Add("UserForm1").Show
End Sub

Fred C.

Re: Passing Var's from Userform to Module

Anonymous — Wed, 21 Dec 2005 15:25:42 GMT

Bear with me but im a little confused. I haven't used the activeworkbook method. I have to add controls at runtime to a multipage on a userform i have already created at design time. So how can i manipulate the syntax you just gave me to fit what i need?

Re: Passing Var's from Userform to Module

fxcastil — Wed, 21 Dec 2005 17:50:28 GMT

These are Excel samples

Try these in Excel to see how to add forms at design time or run time . Run time forms are never visible in the IDE development environment and design time forms are added to your project and are therfore visible in the IDE development environment. To run these in Excel set the macro/trusted source setting to allow acess to VBE project see *.jpg file

I bet this method of adding forms is not nedded in 99.99% of the time. A reason to add controls at design time and not run time. Might be if you had a userform with 100 buttons and 100 labels and 100 frames and you did not to create all these by hand. Or if you had many similar projects which use the identical form except for qty of controls and you want to have a configurable userform

Fred Castillo

Re: Passing Var's from Userform to Module

Anonymous — Wed, 21 Dec 2005 18:48:28 GMT

Thanks for the samples. But what i need is to add controls at runtime inside the IDE in autocad. I am only using excel to get variables from. And the reason the controls need to be added at runtime is that the source file in excel will have different prompts for each file called. So they will be constantly changing. The only things that will remain constant will be the listbox and the OK and Cancel buttons. I just need to know why my code wasn't adding the controls to my existing multipage. See above.

Re: Passing Var's from Userform to Module

fxcastil — Thu, 22 Dec 2005 02:45:27 GMT

The samples I gave are to give you a tutorial on using the
VBA extensibility library which is what you are using to add controls at runtime . The samples are VBA dependent and also work the same in any VBA host such as Autocad.

As far your example you are trying to add the control to a multipage . The userform is the container for the controls not the multi-page . The multipage is another control which also resides on the userform.

Run the examples I gave you they are a good tutor for using the VBA extensibility library

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3BQ204330

http://support.microsoft.com/default.aspx?scid=kb;en-us;185774

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=432

Fred Castillo

Re: Passing Var's from Userform to Module

Anonymous — Thu, 22 Dec 2005 03:01:50 GMT

thanks fred. I think i got it now. Appreciate it.

Re: Passing Var's from Userform to Module

Anonymous — Sat, 24 Dec 2005 17:14:35 GMT

I tried to make it work the other way but had no luck. Byco I tried to do it your way but i think im placing your code into the module incorrectly. Can you update my code and repost it the way you got it to function properly. I'd appreciate it greatly. Thanks alot.

Re: Passing Var's from Userform to Module

Anonymous — Sun, 25 Dec 2005 16:29:40 GMT

Merry Christmas

Re: Passing Var's from Userform to Module

Anonymous — Tue, 27 Dec 2005 14:42:57 GMT

Thanks alot. You are definetly a much better programmer than i am. Although im still learning. There is one issue that still remains though. When you hit the draw view button after you have already clicked it once it doesn't draw. So i can go through and select plan view and select the point on the screen and it draws the view fine, if i want to draw the plan view again without ending the process it will allow me to select the point again but will not draw the geometry over again. I haven't been able to dig through what you did yet from the holidays but i'm gonna atleast try. If you feel like checking into why please do otherwise i'm good with just getting this far. Greatly appreciate what you've done for me this far.

Re: Passing Var's from Userform to Module

Anonymous — Tue, 27 Dec 2005 17:09:47 GMT

Mac you dont want to declare things at the module level if you dont need to for example Do Until Name = "[End]", when Name is declared at the modular level after the first pass its value is End so you cant continue whereas dimming it in the sub its value is reset to "".

Also found that once you set an object =userform1 its best to take all references to userform1 away as they start the Userform_initialize sub.

Re: Passing Var's from Userform to Module

Anonymous — Tue, 27 Dec 2005 20:25:51 GMT

Thanks for the help again. Yeah i knew that about my Do while method. I had fixed that problem earlier so i didn't even think to look for that again. But thanks for the heads up. And also thanks for the knowledge and all the kind help. Much obliged.