User Form

[Guest]

Someone suggested this line of code to be able to use a variable to open a
particular user form but I get a run time error 13 - Type Mismatch. Did I
miss something when putting the code in?? Thanks!

VBA.UserForms("ModConfig" & NT1s).Show

I have several forms with the names only being different by the number on
the end...modconfig1, modconfig2, modconfig3 and so on. Depending on what the
user inputs for the value of NT1s I would like it to open that user form.

 

[Chip Pearson]

I don't think you can user VBA's UserForms object to load and
show a form. It is a collection-like object that contains
references to loaded forms.

In your case, you'll have to write code like

Select Case NT1s
Case 1
ModConfig1.Show
Case 2
ModConfig2.Show
' etc
End Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Stephen Bullen]

Hi Tony,

Someone suggested this line of code to be able to use a variable to open a
particular user form but I get a run time error 13 - Type Mismatch. Did I
miss something when putting the code in?? Thanks!

VBA.UserForms("ModConfig" & NT1s).Show

I have several forms with the names only being different by the number on
the end...modconfig1, modconfig2, modconfig3 and so on. Depending on what the
user inputs for the value of NT1s I would like it to open that user form.

Sorry, I mis-typed the line:

VBA.UserForms.Add("ModConfig" & NT1s).Show

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Thank you very much for the update, it works great now! One other question of
the same sort if you have the time. In each of the forms I have a certain
number of row each with 6 text boxes. Each text box is numbered from left to
right Slot1, Slot2, Slot3 and so on, when it goes to row #2 the numbering
continues Slot7, Slot8,...ect. I have a loop set up that will run for the
NT1s times with another inner loop that runs 6 times (for the number of cards
per row). I am trying to set an array type of variable Card(#,#)with the
value of each slot#. As the loops run I have a counter going and that is what
I would like to use as the variable for the Slot#. Example as follows....

For counter1 = 1 to NT1s
For counter2 = 1 to 6
counter3 = counter3 + 1
Card(counter1,counter2) = modconfig(NT1s).Slot(counter3)
next counter2
next counter1

I put your solution for the user form- vba.userform.add("modconfig" &
NT1s).Slot(counter3) in place of the above but I still get an error. Is there
a way to write it in a way that would make it work or am I going beyond what
it can do.

 

[Stephen Bullen]

Hi Tony,

I am trying to set an array type of variable Card(#,#)with the
value of each slot#. As the loops run I have a counter going and that is what
I would like to use as the variable for the Slot#. Example as follows....

For counter1 = 1 to NT1s
For counter2 = 1 to 6
counter3 = counter3 + 1
Card(counter1,counter2) = modconfig(NT1s).Slot(counter3)
next counter2
next counter1

I put your solution for the user form- vba.userform.add("modconfig" &
NT1s).Slot(counter3) in place of the above but I still get an error. Is there
a way to write it in a way that would make it work or am I going beyond what
it can do.

You're close <g>. Firstly, the userform.add thing is just to open the userform
by name and get a reference to it, which you could store in a variable:

Dim frmTheForm As Object
Set frmTheForm = VBA.Userforms.Add("ModConfig" & NT1s)

Then you can reference a control on the form by name, using the Controls
collection:

Dim frmTheForm As Object

Set frmTheForm = VBA.Userforms.Add("ModConfig" & NT1s)

For counter1 = 1 to NT1s
For counter2 = 1 to 6
counter3 = counter3 + 1
Set Card(counter1,counter2) = frmTheForm.Controls("Slot" & counter3)
next counter2
next counter1

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Well...I am back again. First off just let me say thank you, I have only
written a couple programs so far and tend to get 'hung up' on some of these
commands so all of your help is greatly appreciated!! I think I just about
have this section complete but I am having a problems with something very
simple.....closing the form. I tried using the frmTheForm object

frmTheForm.Hide

and I used the whole line as

VBA.Userform.Add("ModConfig" & NT1s).Hide

but neither works. I am assuming that it is because of the word 'Add' in the
command line but try as I might, I have not figured the working code word.
Thanks again for all the help

 

[Guest]

Just found out I am having the same issue with assigning values to the text
boxes in my forms. Using both examples as below in this way:

frmTheForm.BTSIDTextBox = BTSID

Neither one is assiging the BTSIDTexBox with any value....I will keep trying
though!

 

[Stephen Bullen]

Hi Tony,

I think I just about
have this section complete but I am having a problems with something very
simple.....closing the form.

Where are you trying to close the form from? if it's a Click event of a
control on the form, use 'Me.Hide', or 'Unload Me'.

Just found out I am having the same issue with assigning values to the text
boxes in my forms. Using both examples as below in this way:

frmTheForm.BTSIDTextBox = BTSID

Neither one is assiging the BTSIDTexBox with any value....I will keep trying
though!

The following works for me (assume BTSIDTextBox is the name of a control on the
form):

Sub test()

Dim frmTheForm As Object

Set frmTheForm = VBA.UserForms.Add("Userform1")

frmTheForm.BTSIDTexBox.Text = "Hello"
frmTheForm.Show

End Sub

I am assuming that it is because of the word 'Add' in the
command line but try as I might, I have not figured the working code word.
Thanks again for all the help

To be honest, I'm wondering whether the best course of action for you to take
is to read a good book about creating userforms, such as John Green's "Excel
2002 VBA Programmer's Reference" (to which I contributed some chapters).

The reason I suggest that is the technique of refering to a userform in the way
I've shown is a somewhat advanced one, which I fear you might not be ready to
employ.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

Funny you should mention that, I was going to ask if you might be able to
suggest a good book or two that might be helpful. I was nominated here at
work to learn as
much as I can so we can start automating more of our daily tasks. I have
been able to complete a couple programs but they were less complicated. I
will try to pick up that book before Monday. Thanks again for your help and
suggestions. Take care and have a great weekend!