Passing Var's from Userform to Module

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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, vbNullChar) - 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>

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[fxcastil]

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.

[Anonymous]

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?

[fxcastil]

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

[Anonymous]

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.

[fxcastil]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

Merry Christmas

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.