Help with combo/Listbox

[Les Stout]

Hi all, i have a spreadsheet that i need to insert either a list or
combo box (Not sure which is best), so that the user can sellect a date
(Jan-07 to Dec-07)with the selected date going into "B6". I then also
have to remove the Box as the user must send the sheet by e-mail but the
date in B6 must remain. Could somebody please help me with some code to
do this ??

Les Stout

 

[Coza]

Depending on how you want to trigger the Date to be input, but one method
would be the Range_Selection.

Right Click the Sheet Tab in question and Select the View Code>Main Window
Top Left Drop list Select WorkSheet
Other Drop List select Selection Change

Paste this code in the main window in the line under the :
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

***********************************************
If Target.Address = "$B$6" Then
UserForm1.Show
Else
Exit Sub
End If
***********************************************

Should look like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$6" Then
UserForm1.Show
Else
Exit Sub
End If

End Sub


Then Click on the Icon directly under the File Menu.
This will add a Userform(Userform1) to the list of objects below.
You can shape the Userform (Grey shaded box now in main window to suit.
Using the TOOLBOX add a LISTBOX (Should be 5th icon on top row.

make it about1" wide by 2" long
Then click on the shaded area of the userform again to display the TOOLBOX
Add a Button under the Listbox
Click on the button and name it CANCEL and size it to suit.
Double click the Listbox.
Copy and Paste the code between the astrixes PASTE OVER the text in the main
window on the screen too.

************************************
Private Sub CommandButton1_Click()
ActiveCell.Value = ""
Unload Me
End Sub

Private Sub ListBox1_Click()
ActiveSheet.Range("B6").Value = ListBox1.Value
Range("B6").Value = Format(Range("B6").Value, "mm-yy")
Unload Me
End Sub

Private Sub UserForm_Activate()
With ListBox1
..AddItem "Jan-07"
..AddItem "Feb-07"
..AddItem "Mar-07"
..AddItem "Apr-07"
..AddItem "May-07"
..AddItem "Jun-07"
..AddItem "Jul-07"
..AddItem "Aug-07"
..AddItem "Sep-07"
..AddItem "Oct-07"
..AddItem "Nov-07"
..AddItem "Dec-07"
End With
End Sub
************************************


Click ALT+Q.

Now when the user Selects the cell B6, a box apears and prompts a Date to be
selected.
Clicking on a date places the date in cell B6.


Corey....

 

[Les Stout]

Thanks so much Corey, you are a star !!!

Les Stout

 

[Guest]

Hi Les -

Have you considered a calendar control ? I have yet to find a case where a
combo or listbox outperforms the calendar control for selecting dates. You
can get an outstanding version from Martin Green's web site at:

http://www.fontstuff.com/downloads/index.htm

Download Martin's "Excel Calendar Workbook.zip". Open the workbook, Export
his frmCalendar form, Import it into your workbook, and modify the form code.
Change the three instances of "ActiveCell" to "Range("B6")" to suit your
application.

After doing that, you have several options to trigger the calendar (show it
to the user). Here are two options:

Option 1: You can use Martin's right-click shortcut menu control "Insert
Date". If this is your choice, copy all of the code in Martin's ThisWorkbook
module to your ThisWorkbook module. To choose a date for cell B6, just
right-click in any cell and choose "Insert Date" from the shortcut menu.

A sub-option here is to modify Martin's code to run in the worksheet's
Activate and Deactivate event procedures. Then, the Insert Date option will
only be available from a right-click in the single worksheet.

Option 2: Add a command button to your worksheet from the Control Toolbox,
then:

a. Right-click the button, choose |Format Control...|. Click the
[Properties] tab and clear the "Print Object" checkbox and click [OK].

b. Right-click the button again, choose Properties, and modify the Caption
property to "Enter Date" (or whatever you like on the button face).

c. Right-Click the button again, choose View Code, and add the following
code in the worksheet module that opens:

Private Sub CommandButton1_Click()
frmCalendar.Show
End Sub

Repost if you'd like to take a different approach or if you need help
modifying Martin's code.

 

[Guest]

If you want to use a userform in VBA this is real simple code

1) on VBA insert menu select user form.
2) If toolbox doesn't show up thenlook for ICON with hammer and wrench.
3) From toolbox put listbox on the form.
4) Double click list box A new window should appear with two lines of code.
Add to this code the statementt - Userform1.hide
5) Run the code below from a module window.


Sub xyz()
Mydate = DateValue("1/1/" + CStr(Year(Date)))
Do While (1)
UserForm1.ListBox1.AddItem CStr(Mydate)
If Mydate = DateValue("12/31/" + CStr(Year(Date))) Then Exit Do
Mydate = Mydate + 1

Loop
UserForm1.Show
SelectedDate = UserForm1.ListBox1.Text
msgbox(SelectedDate)

End Sub

 

[Les Stout]

Thanks all for the input, i will try all and use the most suitable...
Great weekend...

Les Stout