Hi there!

I am in need of a macro to allow a user to choose a month from a list of
months (as opposed to letting them type in the month). Because this macro
will be added to another macro program that I already have, I will also need
the user's selection to be saved as a global variable.

For some reason, I was thinking of using something like MsgBox or InputBox,
but you cannot define the buttons to the degree of letting the user actually
click on a particular month. The buttons are set values (Ok, Cancel, etc.)

Any ideas?

Thanks in advance!

Ronny

Have you considered using <data< validation <allow list.
No macro required
You will get a dropdown list to choose from.

--
Greetings from New Zealand
Bill K


"Ronny Hamida" wrote in message
...
Hi there!

I am in need of a macro to allow a user to choose a month from a list of
months (as opposed to letting them type in the month). Because this macro
will be added to another macro program that I already have, I will also
need
the user's selection to be saved as a global variable.

For some reason, I was thinking of using something like MsgBox or
InputBox,
but you cannot define the buttons to the degree of letting the user
actually
click on a particular month. The buttons are set values (Ok, Cancel,
etc.)

Any ideas?

Thanks in advance!

Ronny

Sounds like you need an on-the-fly form created. The code below is called
from the procedure 'TestGetChoice' and has a global variable of varMyMonth.
You should check out John Walkenbach's books at...
http://j-walk.com/ss/books/index.htm
- particularly "Excel 2003 Power Programming With VBA"
http://j-walk.com/ss/books/xlbook25.htm


'/======================================
' START OF MACROS
'/======================================
' On the Fly Choices form
'Concept by John Walchenbach - j-Walk.com
' Creating a UserForm Programmatically:
' http://j-walk.com/ss/excel/tips/tip76.htm
'
'Passed back to the function from the USERFORM
' that has not yet been created.
' That is why an ALL MODULES scope variable
' must be used.
'
Public GetChoice_RET_VAL As Variant
Public varMyMonth As Variant

'/======================================
Sub TestGetChoice()
'Adds choices as defined in Ops array below
Dim aryChoices()
Dim iMaxChoices As Double
Dim strTitle As String

iMaxChoices = 12
strTitle = "Select a Month..."

ReDim aryChoices(1 To iMaxChoices)

aryChoices(1) = "Jan"
aryChoices(2) = "Feb"
aryChoices(3) = "Mar"
aryChoices(4) = "Apr"
aryChoices(5) = "May"
aryChoices(6) = "Jun"
aryChoices(7) = "Jul"
aryChoices(8) = "Aug"
aryChoices(9) = "Sep"
aryChoices(10) = "Oct"
aryChoices(11) = "Nov"
aryChoices(12) = "Dec"

'Array of choices, default choice, title of form
'GetChoice returns the array #, so the 'Choose' function
' is used to select a month. For example, if the
' client selects 'Apr', 4 is returned. The 'Choose'
' function lists "Apr" as the 4th selection, therefore,
' "Apr" is returned in the variable varMyMonth.
varMyMonth = Choose(GetChoice(aryChoices, 1, strTitle), _
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _
"Aug", "Sep", "Oct", "Nov", "Dec")

End Sub

'/======================================
Public Function GetChoice(OpArray, iDefault, strTitle)
'OpArray = array of choices
'iDefault = the default choice, i.e. 1=1st choice in array
'strTitle = title of form
Dim TempForm As Object 'VBComponent
Dim NewOptionButton As Object 'MSForms.OptionButton
Dim NewCommandButton1 As Object 'MSForms.CommandButton
Dim NewCommandButton2 As Object 'MSForms.CommandButton
Dim i As Integer, TopPos As Integer
Dim TopPosStart As Integer, AddlSpace As Integer
Dim MaxWidth As Long, MaxWidth1 As Long
Dim Code As String

'Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

'Create the UserForm
Set TempForm = _
ThisWorkbook.VBProject.VBComponents.Add(3) _
'vbext_ct_MSForm
TempForm.Properties("Width") = 550
TopPosStart = 15
' AddlSpace = 15
AddlSpace = 20

'Add the OptionButtons
TopPos = 4
MaxWidth = 550 'Stores width of widest OptionButton
MaxWidth1 = 0 'variable to keep track of widest caption
For i = LBound(OpArray) To UBound(OpArray)
Set NewOptionButton = _
TempForm.Designer.Controls.Add("forms.OptionButton .1")
With NewOptionButton
.Width = MaxWidth
.Caption = OpArray(i)
.Height = TopPosStart
.Left = 8
.Top = TopPos
.Tag = i
.AutoSize = True
If iDefault = i Then .Value = True
If .Width MaxWidth1 Then MaxWidth1 = .Width
If .Width MaxWidth Then .Width = MaxWidth
End With
TopPos = TopPos + TopPosStart + IIf(MaxWidth1 _
MaxWidth, AddlSpace, 0)
Next i

If MaxWidth1 < MaxWidth Then MaxWidth = MaxWidth1

'/----------Add the OK button-------------
Set NewCommandButton1 = _
TempForm.Designer.Controls.Add("forms.CommandButto n.1")
With NewCommandButton1
.Caption = "OK"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 6
End With
'/-----------------------------------------

'/----------Add the Cancel button----------
Set NewCommandButton2 = _
TempForm.Designer.Controls.Add("forms.CommandButto n.1")
With NewCommandButton2
.Caption = "Cancel"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 28
End With
'/-----------------------------------------

'---Add event-hander subs for the CommandButtons---
Code = ""
Code = Code & "Sub CommandButton1_Click()" & vbCrLf
Code = Code & " Dim ctl" & vbCrLf
Code = Code & " GetChoice_RET_VAL = False" & vbCrLf
Code = Code & " For Each ctl In Me.Controls" & _
vbCrLf
Code = Code & " If TypeName(ctl) = ""OptionButton"" Then" & _
vbCrLf
Code = Code & _
" If ctl Then GetChoice_RET_VAL = ctl.Tag" & _
vbCrLf
Code = Code & " End If" & vbCrLf
Code = Code & " Next ctl" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
Code = Code & "Sub CommandButton2_Click()" & vbCrLf
Code = Code & " GetChoice_RET_VAL=False" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
'/-----------------------------------------

With TempForm.CodeModule
.InsertLines .CountOfLines + 1, Code
End With

'Adjust the form
With TempForm
.Properties("Caption") = strTitle
.Properties("Width") = NewCommandButton1.Left + _
NewCommandButton1.Width + 10

If .Properties("Width") < 160 Then
.Properties("Width") = 160
NewCommandButton1.Left = 106
NewCommandButton2.Left = 106
End If
.Properties("Height") = TopPos + 34
End With

'Show the form
VBA.UserForms.Add(TempForm.Name).Show

'Delete the form
ThisWorkbook.VBProject.VBComponents.Remove _
VBComponent:=TempForm

'Pass the selected option back to the calling procedure
GetChoice = GetChoice_RET_VAL

End Function

'/======================================
' END OF MACROS

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ronny Hamida" wrote:

Hi there!

I am in need of a macro to allow a user to choose a month from a list of
months (as opposed to letting them type in the month). Because this macro
will be added to another macro program that I already have, I will also need
the user's selection to be saved as a global variable.

For some reason, I was thinking of using something like MsgBox or InputBox,
but you cannot define the buttons to the degree of letting the user actually
click on a particular month. The buttons are set values (Ok, Cancel, etc.)

Any ideas?

Thanks in advance!

Ronny

Hi there, Bill!

As a matter of fact, I not only considered it, that's what I'm doing now. I
was just looking for a way to put it all in a macro and lock it so that it's
in one place and not two.

Plus, it just sounded interesting to attempt! :)

Ronny

"Bill Kuunders" wrote:

Have you considered using <data< validation <allow list.
No macro required
You will get a dropdown list to choose from.

--
Greetings from New Zealand
Bill K


"Ronny Hamida" wrote in message
...
Hi there!

I am in need of a macro to allow a user to choose a month from a list of
months (as opposed to letting them type in the month). Because this macro
will be added to another macro program that I already have, I will also
need
the user's selection to be saved as a global variable.

For some reason, I was thinking of using something like MsgBox or
InputBox,
but you cannot define the buttons to the degree of letting the user
actually
click on a particular month. The buttons are set values (Ok, Cancel,
etc.)

Any ideas?

Thanks in advance!

Ronny

Thank you, Gary!

"Gary L Brown" wrote:

Sounds like you need an on-the-fly form created. The code below is called
from the procedure 'TestGetChoice' and has a global variable of varMyMonth.
You should check out John Walkenbach's books at...
http://j-walk.com/ss/books/index.htm
- particularly "Excel 2003 Power Programming With VBA"
http://j-walk.com/ss/books/xlbook25.htm


'/======================================
' START OF MACROS
'/======================================
' On the Fly Choices form
'Concept by John Walchenbach - j-Walk.com
' Creating a UserForm Programmatically:
' http://j-walk.com/ss/excel/tips/tip76.htm
'
'Passed back to the function from the USERFORM
' that has not yet been created.
' That is why an ALL MODULES scope variable
' must be used.
'
Public GetChoice_RET_VAL As Variant
Public varMyMonth As Variant

'/======================================
Sub TestGetChoice()
'Adds choices as defined in Ops array below
Dim aryChoices()
Dim iMaxChoices As Double
Dim strTitle As String

iMaxChoices = 12
strTitle = "Select a Month..."

ReDim aryChoices(1 To iMaxChoices)

aryChoices(1) = "Jan"
aryChoices(2) = "Feb"
aryChoices(3) = "Mar"
aryChoices(4) = "Apr"
aryChoices(5) = "May"
aryChoices(6) = "Jun"
aryChoices(7) = "Jul"
aryChoices(8) = "Aug"
aryChoices(9) = "Sep"
aryChoices(10) = "Oct"
aryChoices(11) = "Nov"
aryChoices(12) = "Dec"

'Array of choices, default choice, title of form
'GetChoice returns the array #, so the 'Choose' function
' is used to select a month. For example, if the
' client selects 'Apr', 4 is returned. The 'Choose'
' function lists "Apr" as the 4th selection, therefore,
' "Apr" is returned in the variable varMyMonth.
varMyMonth = Choose(GetChoice(aryChoices, 1, strTitle), _
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _
"Aug", "Sep", "Oct", "Nov", "Dec")

End Sub

'/======================================
Public Function GetChoice(OpArray, iDefault, strTitle)
'OpArray = array of choices
'iDefault = the default choice, i.e. 1=1st choice in array
'strTitle = title of form
Dim TempForm As Object 'VBComponent
Dim NewOptionButton As Object 'MSForms.OptionButton
Dim NewCommandButton1 As Object 'MSForms.CommandButton
Dim NewCommandButton2 As Object 'MSForms.CommandButton
Dim i As Integer, TopPos As Integer
Dim TopPosStart As Integer, AddlSpace As Integer
Dim MaxWidth As Long, MaxWidth1 As Long
Dim Code As String

'Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

'Create the UserForm
Set TempForm = _
ThisWorkbook.VBProject.VBComponents.Add(3) _
'vbext_ct_MSForm
TempForm.Properties("Width") = 550
TopPosStart = 15
' AddlSpace = 15
AddlSpace = 20

'Add the OptionButtons
TopPos = 4
MaxWidth = 550 'Stores width of widest OptionButton
MaxWidth1 = 0 'variable to keep track of widest caption
For i = LBound(OpArray) To UBound(OpArray)
Set NewOptionButton = _
TempForm.Designer.Controls.Add("forms.OptionButton .1")
With NewOptionButton
.Width = MaxWidth
.Caption = OpArray(i)
.Height = TopPosStart
.Left = 8
.Top = TopPos
.Tag = i
.AutoSize = True
If iDefault = i Then .Value = True
If .Width MaxWidth1 Then MaxWidth1 = .Width
If .Width MaxWidth Then .Width = MaxWidth
End With
TopPos = TopPos + TopPosStart + IIf(MaxWidth1 _
MaxWidth, AddlSpace, 0)
Next i

If MaxWidth1 < MaxWidth Then MaxWidth = MaxWidth1

'/----------Add the OK button-------------
Set NewCommandButton1 = _
TempForm.Designer.Controls.Add("forms.CommandButto n.1")
With NewCommandButton1
.Caption = "OK"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 6
End With
'/-----------------------------------------

'/----------Add the Cancel button----------
Set NewCommandButton2 = _
TempForm.Designer.Controls.Add("forms.CommandButto n.1")
With NewCommandButton2
.Caption = "Cancel"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 28
End With
'/-----------------------------------------

'---Add event-hander subs for the CommandButtons---
Code = ""
Code = Code & "Sub CommandButton1_Click()" & vbCrLf
Code = Code & " Dim ctl" & vbCrLf
Code = Code & " GetChoice_RET_VAL = False" & vbCrLf
Code = Code & " For Each ctl In Me.Controls" & _
vbCrLf
Code = Code & " If TypeName(ctl) = ""OptionButton"" Then" & _
vbCrLf
Code = Code & _
" If ctl Then GetChoice_RET_VAL = ctl.Tag" & _
vbCrLf
Code = Code & " End If" & vbCrLf
Code = Code & " Next ctl" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
Code = Code & "Sub CommandButton2_Click()" & vbCrLf
Code = Code & " GetChoice_RET_VAL=False" & vbCrLf
Code = Code & " Unload Me" & vbCrLf
Code = Code & "End Sub" & vbCrLf
'/-----------------------------------------

With TempForm.CodeModule
.InsertLines .CountOfLines + 1, Code
End With

'Adjust the form
With TempForm
.Properties("Caption") = strTitle
.Properties("Width") = NewCommandButton1.Left + _
NewCommandButton1.Width + 10

If .Properties("Width") < 160 Then
.Properties("Width") = 160
NewCommandButton1.Left = 106
NewCommandButton2.Left = 106
End If
.Properties("Height") = TopPos + 34
End With

'Show the form
VBA.UserForms.Add(TempForm.Name).Show

'Delete the form
ThisWorkbook.VBProject.VBComponents.Remove _
VBComponent:=TempForm

'Pass the selected option back to the calling procedure
GetChoice = GetChoice_RET_VAL

End Function

'/======================================
' END OF MACROS

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Ronny Hamida" wrote:

Hi there!

I am in need of a macro to allow a user to choose a month from a list of
months (as opposed to letting them type in the month). Because this macro
will be added to another macro program that I already have, I will also need
the user's selection to be saved as a global variable.

For some reason, I was thinking of using something like MsgBox or InputBox,
but you cannot define the buttons to the degree of letting the user actually
click on a particular month. The buttons are set values (Ok, Cancel, etc.)

Any ideas?

Thanks in advance!

Ronny