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

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

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

Just a correction...

This formula:
=INDIRECT(IF(width=0.7,"range1","range2"))
can be:
=IF(width=0.7,range1,range2)

No need for that extra junk.

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