I give the cells that are used for input a fill color of light yellow--to stand
out.
If that's not enough, maybe you could add a shape to the cell that looks like a
dropdown.
Alternatively, maybe you could use an event macro that changes that input range.
For instance, if you're changing Width by typing, you could tie into the
worksheet_Change event to change the input range for that dropdown (from the
Forms Toolbar, right????):
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDD As DropDown
If Intersect(Target, Me.Range("width")) Is Nothing Then
Exit Sub
End If
Set myDD = Me.DropDowns("Drop Down 1")
With myDD
'clear the current choice
'since the list is changing
.ListIndex = 0
If Me.Range("Width").Value = 0.7 Then
.ListFillRange = Me.Range("Range1").Address(external:=True)
Else
.ListFillRange = Me.Range("Range2").Address(external:=True)
End If
End With
End Sub
This assumes that all the ranges (width, range1, and range2) are all on the same
sheet.
Some references:
David McRitchie's intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
PCH wrote:
Dave
It needs to be a combo box just by the fact that a conbo box has a pull down
icon and the data validation does not untill you click in it.
I know it's no too much of an ask to have people select the cell then the
value, but you'd be supprised on the number of phone calls....
"Dave Peterson" wrote:
Do you have to use a combobox?
How about a cell with Data|Validation?
Then you could use List with a source:
=INDIRECT(IF(width=0.7,"range1","range2"))
(notice that the range1 and range2 names are surrounded by quotation marks.)
PCH wrote:
I have a combobox on a work sheet.
I want to set the input range depending upon an if statement...
Input range =if(Width=0.7,Range1,Range2)
typing this statement into the input range does not work.
How can I achieve this?
regards
Wayne
--
Dave Peterson
--
Dave Peterson