I have a worksheet selection change event that calls a user form. Within the
user form, I want to display the value that is one column to the left of the
cell that is selected. How would I do that?

Thanks

Private Sub Userform_Initialize()
if activecell.column < 1 then
Textbox1.Value = activecell.offset(0,-1).value
End if
End sub

If you want it more sophisticated than that, you would have to make a public
variable declared in a general module to hold a reference to target (set in
the selectionchange event) and accessed by the userform in the initialize or
activate event.

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote:

I have a worksheet selection change event that calls a user form. Within the
user form, I want to display the value that is one column to the left of the
cell that is selected. How would I do that?

Thanks

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:

I have a worksheet selection change event that calls a user form. Within the
user form, I want to display the value that is one column to the left of the
cell that is selected. How would I do that?

Thanks

Whoops, posted to the wrong question.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

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:

I have a worksheet selection change event that calls a user form. Within the
user form, I want to display the value that is one column to the left of the
cell that is selected. How would I do that?

Thanks