Data from User Forms

Shaun · Jan 17, 2013

Hi Everyone

I am preparing a user form which incorporates date, time and then selections from drop down lists. The first list is 1 to 3, at this point I would like the second list to be generated from the first, the code:

[pre]

Code:

Private Sub UserForm_Activate()
FormDate = Format(Now(), &#34;DD MMMM YYYY&#34;)
FormTime = Format(Now(), &#34;H:MM:SS am/pm&#34;)

With Number
.AddItem &#34;1&#34;
.AddItem &#34;2&#34;
.AddItem &#34;3&#34;
End With

With Number2
If Number.Value = &#34;1&#34; Then
.AddItem &#34;4&#34;
Else
If Number = &#34;2&#34; Then
.AddItem &#34;5&#34;
.AddItem &#34;6&#34;
.AddItem &#34;7&#34;
.AddItem &#34;8&#34;
.AddItem &#34;9&#34;
.AddItem &#34;10&#34;
Else
If Number = &#34;3&#34; Then
.AddItem &#34;11&#34;
.AddItem &#34;12&#34;
.AddItem &#34;13&#34;
.AddItem &#34;14&#34;
.AddItem &#34;15&#34;
Else
End If
End If
End If
End With
End Sub

[/pre]

The problem I have is that I cannot work out is how to generate Number2 list based on Number list. Just thinking as I type, I probably need to have a second user form as it would not be possible to generate list Number2 "on the fly" using this method.

The other issue I am having is that I don't know how to get the data from the form into the spread sheet, are there any good tutorials? I haven't been able to find any.

Cheers

Shaun

SirJB7 · Jan 17, 2013

Hi, Shaun!

Give a look at this file:

https://dl.dropbox.com/u/60558749/Data%20from%20User%20Forms%20%28for%20Shaun%20at%20chandoo.org%29.xlsm

It has an user form with two list controls, I think it behaves as you wanted. This is your updated code:

-----

[pre]

Code:

Option Explicit

Private Sub Number_Change()
&#39; constants
&#39; declarations
&#39; start
&#39; process
&#39;  second list
With Number2
.Clear
If Number.Value = &#34;1&#34; Then
.AddItem &#34;4&#34;
Else
If Number = &#34;2&#34; Then
.AddItem &#34;5&#34;
.AddItem &#34;6&#34;
.AddItem &#34;7&#34;
.AddItem &#34;8&#34;
.AddItem &#34;9&#34;
.AddItem &#34;10&#34;
Else
If Number = &#34;3&#34; Then
.AddItem &#34;11&#34;
.AddItem &#34;12&#34;
.AddItem &#34;13&#34;
.AddItem &#34;14&#34;
.AddItem &#34;15&#34;
Else
End If
End If
End If
End With
End Sub

Private Sub UserForm_Activate()
&#39; constants
&#39; declarations
Dim FormDate As String, FormTime As String
&#39; start
FormDate = Format(Now(), &#34;DD MMMM YYYY&#34;)
FormTime = Format(Now(), &#34;H:MM:SS am/pm&#34;)
&#39; process
&#39;  first list
With Number
.AddItem &#34;1&#34;
.AddItem &#34;2&#34;
.AddItem &#34;3&#34;
End With
&#39;  second list
With Number2
If Number.Value = &#34;1&#34; Then
.AddItem &#34;4&#34;
Else
If Number = &#34;2&#34; Then
.AddItem &#34;5&#34;
.AddItem &#34;6&#34;
.AddItem &#34;7&#34;
.AddItem &#34;8&#34;
.AddItem &#34;9&#34;
.AddItem &#34;10&#34;
Else
If Number = &#34;3&#34; Then
.AddItem &#34;11&#34;
.AddItem &#34;12&#34;
.AddItem &#34;13&#34;
.AddItem &#34;14&#34;
.AddItem &#34;15&#34;
Else
End If
End If
End If
End With
&#39; end
End Sub

[/pre]
-----

Just advise if any issue.

Regards!

PS: Please always use Option Explicit statement, it's safe and avoid unwanted typo errors.

Shaun · Jan 17, 2013

Hi SirJB7

The file you have uploaded behaves exactly as I had tried to achieve.

Thank you very much.

Cheers

Shaun

SirJB7 · Jan 17, 2013

Hi, Shaun!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

Data from User Forms

Shaun

Member

SirJB7

Excel Rōnin

Shaun

Member

SirJB7

Excel Rōnin