IF statement with combo boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to add an IF statement to a cell so that when a certain phrase is typed in, it will use different named ranges. Example, if I type in the word Calendar to B5, then the 12 month calendar list will be showing in the combo box in B6. If I then type in Fiscal in B5, then the fiscal year calendar list will show in the combo box in B6 (end example). Is this possible? I have four different potential named ranges that could be displayed in B6 if B5 contains one of the key words to make it appear.
 
Debbie,

Here is some VBA to do it. Put all of this code into the worksheet code
module. Make sure that the named ranges are the same as what will be typed
into B56 (calendar, fiscal, etc.), and when the value of B5 changes, the
validation in B6 will change to point at a different range. Change the Case
statement to add the other values/ranges.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("B5")) Is Nothing Then
With Target
Select Case LCase(.Value)
Case "calendar", "fiscal":
LoadDates (LCase(.Value))
Case Else:
MsgBox "Invalid value", vbCritical, "Date Validator"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub LoadDates(RangeName As String)
With ActiveSheet.Range("B6").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & RangeName
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub




--

HTH

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

Debbie said:
I want to add an IF statement to a cell so that when a certain phrase is
Click to expand...
typed in, it will use different named ranges. Example, if I type in the word
Calendar to B5, then the 12 month calendar list will be showing in the combo
box in B6. If I then type in Fiscal in B5, then the fiscal year calendar
list will show in the combo box in B6 (end example). Is this possible? I
have four different potential named ranges that could be displayed in B6 if
B5 contains one of the key words to make it appear.
 
Thank you, Mr. Phillips. I have another question. I've
typed in the named ranges to match the info to be typed in
B5. Now I'm wondering where I should change the Case
statement to add the other ranges as you stated in your
reply. Can you be more specific on where that is in the
VBA that you sent? Thank you again
Debbie
 
Debbie,

The name is Bob!

This line is the one to change

Case "calendar", "fiscal":

calendar, and fiscal is your named ranges. If you have four called A, B, C,
D, it would be

Case "A", "B", "C", "D":

Get the idea?

--

HTH

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