Using IF, Then with rowsource?

[CAA]

Does anybody know why i can't do this

If ShN > 4 And ShN < 9 Then
shHeight1.Enabled = False
shHeight1.BackColor = &HA49597
ShHang1.RowSource = "Sheet1!B50:B56"
Else
shHeight1.Enabled = False
shHeight1.BackColor = &HF9F8EC
ShHang1.RowSource = Sheets("Window Styles").Range("C50:C57")
End If

Hope somebody can shed some light for me.
Thanks
CA

 

[Bob Phillips]

You don't say what the problem is. If it is this line

ShHang1.RowSource = Sheets("Window Styles").Range("C50:C57")

try

ShHang1.RowSource = "'" & Sheets("Window Styles").Name & "'!" &
Sheets("Window Styles").Range("C50:C57").Address

--

HTH

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

 

[CAA]

Sorry Bob
However a guru like yourself can spot these problems withou
explanation ;-)

The problem was it didn't populate my combobox.
I tried your piece of code and still nothing, so I trimmed out th
first bit and then realised that an erlier attempt only needed th
.Address at the end. Which after trimming yours was exactly what
needed.

I need to read more about the references cells, previously I had gotte
away with just "Sheet1!B50:B56" but had never ttried within an i
statement to set it at runtime, quirky stuff.

Thanks alot for your help, my day has just become lighter.
CA

 

[Bob Phillips]

The Rowsource is a string property and you were setting it to a range. This
part, Sheet1!B50:B56" , is a nice simple string, when using a reference to
another part of a worksheet it is not so obvious is it.

Anyway, glad you are sorted now.

--

HTH

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

 

[Dave Peterson]

I find this style easier to type and read:

ShHang1.RowSource _
= Sheets("Window Styles").Range("C50:C57").address(external:=true)

 

[CAA]

Cheers Dave

That's nearly exactly as i've put it, however i don't have a clue wha
the (external = true) is for, I looked it up and found it mean
external referance.
I don't know what that means for excel, I use Autocad and an externa
referance is part of the drawing that is a copy of another drawing
when the other is updated then the changes are reflected in you
current drawing, is this similar for Excel?


CA

 

[Dave Peterson]

Try this:

with activesheet
msgbox .range("a1").address
msgbox .range("a1").address(0,0)
msgbox .range("a1").address(external:=true)
end with

And you'll see what it does.

 

[Tom Ogilvy]

Address returns a string showing the address of the range. The optional
arguments ajdust the appearance of that address.

by default it is

With Workbooks("Book1.xls")
Set rng = .Worksheets("Sheet5").Range("B9:C12")
End With

rng.Address would return
$B$9:$C$12

rng.Address(external:=True) would return
[Book1.xls]Sheet5!$B$9:$C$12

So External really means fully qualified - the thought being that you
wouldn't need that information unless the reference was to a workbook
external to the current one.

 

[CAA]

Well, I've learnt quite a bit And all is very handy stuff

As it turned out Bob gave me the right answer for my situation th
fisrt time round.
I had trimmed off the first part and because my form has model=Fals
whenever i changed worksheets the combobox rowsource changed.
Thanks alot for the help guys
CA