Input Box List

[pauluk]

Hi Peeps,

What i want to to do is have a list to select from. I use a input bo
for monthly outputs where you type the actuall month. Instead of doin
this i want a list box so that you just clcik on the month then ok. Th
code am using is as follows:

Dim rng As Range
Dim rng2 As Range
Dim strMONTH As String
strMONTH = Application.InputBox("Please Select the month you wish t
compare.", "Monthly Output")

Selection.AutoFilter Field:=10, _
Criteria1:="=" & (strMONTH)

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No info for that month"
Else
Worksheets("Monthly Output").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Monthly Output").Range("A1")
End If
ActiveSheet.ShowAllDat

 

[Bob Phillips]

Use a form with a listbox and load the month names.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[pauluk]

I am not really that sure on how to apply this

 

[Bob Phillips]

Just create a userform and add this code to it

Private Sub ListBox1_Click()
MsgBox "Month " & ListBox1.ListIndex + 1 & ", " & ListBox1.Value
End Sub

Private Sub UserForm_Activate()
With Me.ListBox1
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
.ListIndex = 0
End With
End Sub

The click event is where you would effect your realo code, this is just to
show what is rqeuired.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

Peter Aiken Articles:
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFormsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFormsPartII.asp

 

[David]

Bob Phillips wrote

Private Sub UserForm_Activate()
With Me.ListBox1
.AddItem "January"
.AddItem "February"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "November"
.AddItem "December"
.ListIndex = 0
End With
End Sub

A method I use:

Private Sub UserForm_Activate()
For i = 1 To 12
Me.ListBox1.AddItem MonthName(i)
Next
End Sub

 

[Bob Phillips]

Yeah that works too<g>.

Just reeled it off in response to the OP as he seemed lost.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)