Userform, listbox and ActiveCell. I'm stumped

[Guest]

My project, after many weeks, is nearly finished. I have just got to input
some date values.

I am going to use a right click event to open a userform with a listbox then
the user can click on an entry in the listbox. Then the userform closes with
the value clicked on in the ActiveCell(or cell that was right clicked on to
open userform).

I have trawled through many online tutorials but non seem to deal with this
kind of problem. The data is todays date +- 5 working days In 'C!A10:A20'.
That I have dealt with. I just need to get the yuserform to work. Any help
would be greatly appreciated...

 

[Guest]

Hi,

Try this:
to load userform :
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
UserForm1.Show 0
End Sub

To close userform:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If UserForm1.Visible Then If Target.Value = "Close" Then Unload UserForm1
End Sub


Rgds,

Halim

 

[NickHK]

Dave,
Not sure what you exact problem is or the relevance of 'C!A10:A20', but

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
UserForm1.Show vbModal
End Sub

'UserForm1 code
Private Sub CommandButton1_Click()
ActiveCell.Value = Now() + 5
Unload Me
End Sub

NickHK

 

[Guest]

The First part of the code works fine and the userform opens up. But when i
click on a date in the list box it does not automatically input the value i
clicked on. Is it possible to do this or will I just have to use a command
button below it? So highlight the value and click on a button.

 

[halimnurikhwan]

Hi ,

Try this one :

Private Sub ListBox1_Click()
ActiveCell.Value = ListBox1.Value
End Sub


Rgds,

Halim


DaveyJones menuliskan: