Input Box List
 

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

--
Message posted from http://www.ExcelForum.com

Input Box List
 

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 " wrote in message
...
Hi Peeps,

What i want to to do is have a list to select from. I use a input box
for monthly outputs where you type the actuall month. Instead of doing
this i want a list box so that you just clcik on the month then ok. The
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 to
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.ShowAllData


---
Message posted from http://www.ExcelForum.com/

Input Box List
 

I am not really that sure on how to apply this

--
Message posted from http://www.ExcelForum.com

Input Box List
 

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)

"pauluk " wrote in message
...
I am not really that sure on how to apply this.


---
Message posted from http://www.ExcelForum.com/

Input Box List
 

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...FormsPartI.asp
Part II
http://msdn.microsoft.com/library/en...ormsPartII.asp

--
Regards,
Tom Ogilvy

"pauluk " wrote in message
...
I am not really that sure on how to apply this.


---
Message posted from http://www.ExcelForum.com/

Input Box List
 

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

--
David

Input Box List
 

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)

"David" wrote in message
...
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

--
David