Excel: UserForm only populates if source list sheet is showing 3

[MeGustaXL]

Hi All

My UserForm is initialised from lists on a worksheet named “Data Lists” using this code:

Public Sub UserForm_Initialize()
With cboSerialNo
.RowSource = shDataList.[Serial_No].Address
.ListIndex = 0
End With
With cboTailNo
.RowSource = shDataList.[HarTail].Address
.ListIndex = 0
End With
With cboUnit
.RowSource = shDataList.[Units].Address
.ListIndex = 0
End With
With cboDateON
.RowSource = shDataList.[Date].Address
.ListIndex = 1
End With
With cboDateOFF
.RowSource = shDataList.[Date].Address
.ListIndex = 1
End With
End Sub

The shDataList part of the RowSource property is the Worksheet Reference Name, created as per Bowers74’s FAQ707-4090, as are the references to Named ranges in square brackets, e.g. “[Date]”.

I have another sheet in the Wb called “Current”. Both this sheet and “Data Lists” have a button which shows the form.

My problem is this: if I call the form with “Data Lists” showing, all the boxes initialise correctly, values are filled in where appropriate, and calculated fields all work.

If I call the form from “Current”, the boxes are blank, no values appear, and I get the error “Type Mismatch” from one of the Change() event lines.

Is this because the Named Ranges [Date], [Units], [Serial_No] etc are Worksheet-Level names? If so, how can I either (a) Workaround it, or (b) make them Workbook-level names?

Hope you can bail me out (yet again!)

Chris

 

[TonyJollans]

Hi relmanz2000,

Your named ranges represent absolute ranges. The problem is with the use of the Address property; by default it returns a 'local' address (e.g. A1:B5). Once you have set the RowSource property, the Userform doesn't know anything about the named range - it just has an address to work with and it does all it can do and assume it to be on the active sheet. To generate a qualified address for your RowSource, use the External option of the Address property ...

.RowSource = shDataList.[Serial_No].Address(External:=True)

This should give your RowSource an address like "[Book1.xls]Sheet1!A1:B5" which is independent of the active sheet.

Enjoy,
Tony

 

[MeGustaXL]

Splendid! Thanks Tony

I'm really gonna have to sit down with the VBA Programming Manual one day and read it from cover to cover - NOT!

Working Macros=

Chris

 

[Bowers74]

Thank you for that Tony!

I never knew about the External reference! Helps me!

Have a star for that!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Thanks, both. Glad I could help.

Enjoy,
Tony

 

[SkipVought]

TJ,

For someone who is just coding for the "love of the hunt"...

what can I say? Do you read that manual every nite before you say your prayers? You are amazing!

You don't know how many times I coded around that. I learn so much from ALL of you!

==> *



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Bowers74]

Chris,

I am glad to see that SOMEONE is taking my thread707-642389 to heart!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Thanks Skip,

I'm happy to pass on what I know but I learn lots here too, that's why I stay.

Bedtime reading at the moment is JavaScript, and I don't have any time for prayers

Enjoy,
Tony