VBA Addin and range names
 

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

VBA Addin and range names
 

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

--

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


" wrote:

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

VBA Addin and range names
 

On Apr 2, 10:31*pm, Air_Cooled_Nut
wrote:
If I understand correctly, you have a range named DefaultUnitsRange. *If so,
then:
* *.RowSource = Range("DefaultUnitsRange")

--

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



" wrote:
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- Hide quoted text -

- 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("DefaultUni tsRange") 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

VBA Addin and range names
 

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.
--

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


" wrote:

On Apr 2, 10:31 pm, Air_Cooled_Nut
wrote:
If I understand correctly, you have a range named DefaultUnitsRange. If so,
then:
.RowSource = Range("DefaultUnitsRange")

--

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



" wrote:
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- Hide quoted text -

- 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("DefaultUni tsRange") 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

VBA Addin and range names
 

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.

wrote:

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

--

Dave Peterson