Accessing selection list within a user form
 

Let me preface this by saying that I'm a novice with user forms.

Since Excel does interesting things with dates (entry of 9/01 results in
9/1/2006), I want to ensure that the dates are entered as desired. I'm
doing this with user form and a selection change event on the cells that have
dates. If someone has a better idea, let me know.

Anyway, I have a list with months in it and a list with years in it.

Month: Dates!A2:A13
Year: Dates!B2:B32

How do I set up a selection list in the user form?

Thanks

Accessing selection list within a user form
 

I've figured this out.

"Barb Reinhardt" wrote:

Let me preface this by saying that I'm a novice with user forms.

Since Excel does interesting things with dates (entry of 9/01 results in
9/1/2006), I want to ensure that the dates are entered as desired. I'm
doing this with user form and a selection change event on the cells that have
dates. If someone has a better idea, let me know.

Anyway, I have a list with months in it and a list with years in it.

Month: Dates!A2:A13
Year: Dates!B2:B32

How do I set up a selection list in the user form?

Thanks

Accessing selection list within a user form
 

you want one listbox with each month year combination?

With worksheets("Dates")
for each cell in .Range("B2:B32")
for each i = 1 to 12
useform1.listbox1.AddItem format(DateSerial(cell,i,1),"m/yy")
Next
Next
End with

then in you click event for the listbox

Private Sub Listbox1_click()
Dim s as String, iloc as Long, m as String, y as String
s= Listbox1.Value
iloc = Instr(1,s,"/",vbTextcompare)
m = Left(s,iloc-1)
y = "20" & Right(s,len(s)-iloc)
dt = DateSerial(clng(y),clng(m),1)
activecell.Value = dt
activecell.Numberformat = "m/yy"
End Sub

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

Let me preface this by saying that I'm a novice with user forms.

Since Excel does interesting things with dates (entry of 9/01 results in
9/1/2006), I want to ensure that the dates are entered as desired. I'm
doing this with user form and a selection change event on the cells that have
dates. If someone has a better idea, let me know.

Anyway, I have a list with months in it and a list with years in it.

Month: Dates!A2:A13
Year: Dates!B2:B32

How do I set up a selection list in the user form?

Thanks