VBA Addin and range names

[wagee]

Obviously new to VBA....worked my finger to the bone getting my first
VBA program to run. Decided to make it an addin for distribution and
of-course am now having all kinds of problems getting the code to run.
Seems I am refering to workbooks a lot now<ng>

Dummie = MsgBox(msg, vbYesNo + vbExclamation, "Setting Units")

Select Case Dummie
Case vbYes
Workbooks("test-
macro19.xla").Sheets("mike").Range("A15").Value = True

DefaultUnits1.Show

Workbooks("test-
macro19.xla").Sheets("mike").Range("A15").Value = False

End Select

Got that to work but how do I reference named ranges? For instance
this code is from DefaultUnits1 called above:

With DefaultUnits1.ListBox1
.RowSource = "DefaultUnitsRange"
.ColumnHeads = True
.ColumnCount = 3
.ListStyle = fmListStylePlain
End With

I cannot get rowsource to read the range no matter what I have tried.

Can someone give me a lesson on what I should know here?

Thanks

Wayne

 

[Air_Cooled_Nut]

If I understand correctly, you have a range named DefaultUnitsRange. If so,
then:
.RowSource = Range("DefaultUnitsRange")

 

[wagee]

If I understand correctly, you have a range named DefaultUnitsRange.  Ifso,
then:
   .RowSource = Range("DefaultUnitsRange")

--

   Toby Erkson
   http://excel.icbm.org/














- Show quoted text -

Thanks very much for the reply however it did not help.

The error I recieved with the code my was was;"Runtime error 380.
Could not set the RowSource property. Invalid property value"

Now I assume this is because I now have book1 active instead of test-
marco19.xls where I wrote the code. It is an .xla now anyway.

When I add the range object I get this error:"Runtime error 1004.
Method 'Range' of object '_Global' failed"

I assume I must reference WHERE this named range is but I don't know
how to do it. I tried Workbooks.Sheets.Range: Workbooks("test-
macro19.xla").Sheet("Tool_Code").Range("DefaultUnitsRange") but that
gave me a subscript out of range error....thank God as if that was
what was wrong I might as well start programming over.

I am missing a technique that is required when creating VBA code that
is destined to be an Addin. To me it is similar to using relative (to
the active workbook) pathnames and absolute (to where my macros are)
pathnames?

Any help is appreciated.

Wayne

 

[Air_Cooled_Nut]

My bad, I looked too quickly at it. The .RowSource can be...picky...and I
haven't dealt with it so, sorry, I don't have an answer.

Have you checked out this thread?: Forms - PITA?!!! (Rant Alert!)
It's in this news group, try a search if you haven't seen it yet. Jon
Peltier (MVP) has some feedback about this.

 

[Dave Peterson]

Is that range named "DefaultUnitsRange" in the same workbook that holds the
code?

If yes, try:

..RowSource = thisworkbook.worksheets("changethesheetnamehere") _
.range("DefaultUnitsRange").address(external:=true)

Make sure you use the correct worksheet that holds that range.