Referring to ThisWork in an Add-in

[bryanbayfield]

This is bloody infuriating.

I've designed an add-in that incorporates an address book. The entries in the address book are stored in one of the sheets in the add-in. The user is meant to interface with the address book by means of a user form - address book entries are shown in a list box on the form.

I've tried to populate the list box with the entries from the AddressBook sheet of the add-in workbook, like so:

set ws = ThisWorkbook.Sheets("AddressBook&quot
ws.range("a2", ....).sort Key1:=...

Excel doesn't even get to the sort command. It stops at the first command saying that the range methoid of teh workbook object has failed.

I'm having problems in other areas as well trying to reference back to the workbook which constitutes the add-in. It doesn't make any sense! X-)

Help much appreciated.

Thanks.

Bryan.

 

[jebry]

Hi!

All of the range you reference must be included in the same string, even if you reference multiple ranges. So you should use:

ws.Range("a2,...&quot or ws.Range("a2" & ",...&quot

Maybe I've misinterpreted what you are doing here?

hth
Jeff Bridgham

 

[bryanbayfield]

Jeff:

The type of reference I'm using is:

range(top_left_cell, bottom_right_cell)

eg: range("a1",range("a1&quot.end(xlDown))

I don't think creating a rectangular range is the problem - I could be wrong though!

I think the problem I'm having is being able to refernce data contained ThisWorkbook (where ThisWorkbook is the add-in).

Sorry if I confused you.

Bryan.

 

[ide]

i think the trouble is in the range reference:

Set mySheet = Application.ThisWorkbook.Worksheets(1)
Set myRange = mySheet.Range("a1", mySheet.Range("a1&quot.End(xlDown))

or yours:
set ws = ThisWorkbook.Sheets("AddressBook&quot
ws.range("a2", ws.range("a1&quot.end(xlDown)).sort Key1:=...

ide

 

[bryanbayfield]

Thanks ide, completely solved the problem for me.

I got into the mode of thinking that referencing the workbook/sheet before the first instance of "range" was enough. Sometimes the problem is only obvious once it's pointed out to you!

Thanks mate.

Bryan.