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