Sort in hidden page crach

[Ludo]

Hi all,

I'm trying to sort a column (column T) in a hidden page (Add In
property = True).
I found some code on the net, but i always get a Error 9 message
(Subscript out of range (Error 9).
When i set the IsAddin property to False, everything's right, but as
soon as i set it back to True, i'll get the error message.

What am i doing wrong?

Using win XP & Excel2003 SP3.

here's the code i use:

'sort
With ThisWorkbook.Sheets("common settings").Range("T1" )
.Cells.Sort Key1:=Sheets("common settings").Range("T1"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

following 'version' of the code generates the same error:

'sort
With ThisWorkbook.Sheets("Common Settings").Range("T:T")
.Cells.Sort Key1:=Sheets("Common Settings").Range("T:T"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With


Any help welcome.

Regards,
Ludo

 

[Claus Busch]

Hi Ludo,

Am Mon, 21 May 2012 06:27:44 -0700 (PDT) schrieb Ludo:

With ThisWorkbook.Sheets("common settings").Range("T1" )
.Cells.Sort Key1:=Sheets("common settings").Range("T1"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

try:
With ThisWorkbook.Sheets("common settings")
Range("T1").Sort Key1:=.Range("T1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With


Regards
Claus Busch

 

[Ludo]

Op maandag 21 mei 2012 15:57:33 UTC+2 schreef Claus Busch het volgende:

Hi Ludo,

Am Mon, 21 May 2012 06:27:44 -0700 (PDT) schrieb Ludo:


try:
With ThisWorkbook.Sheets("common settings")
Range("T1").Sort Key1:=.Range("T1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With


Regards
Claus Busch

Hallo Claus,

Thanks for the fast reply, but i got an error message.
This time got get:
'Run time error 1004'
Sort method of Range class failed.

But when i place a point (.) before the word Range, it works great.


With ThisWorkbook.Sheets("common settings")
.Range("T1").Sort Key1:=.Range("T1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With

Thanks a lot for your help.

Regards,
Ludo

 

[Ludo]

Hi Ludo,

Am Mon, 21 May 2012 06:27:44 -0700 (PDT) schrieb Ludo:


try:
With ThisWorkbook.Sheets("common settings")
    Range("T1").Sort Key1:=.Range("T1"), Order1:=xlAscending, _
        Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
End With

Regards
Claus Busch

Thanks Claus for the fast reply.

But there's still an error message (method of Class sort failed).
error 1004.


When i place a point (.) before the word Range, it works great, see
code below.

With ThisWorkbook.Sheets("common settings")
.Range("T1").Sort Key1:=.Range("T1"), Order1:=xlAscending, _
'added a point (.) just before the word Range.
Header:=xlNo, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With

Thanks again,
Ludo

 

[GS]

Ludo,
I'm sure Claus meant for the period to be there. I was going to post to
that but I'm glad you found the typo and corrected it yourself<g>!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ludo]

Ludo,
I'm sure Claus meant for the period to be there. I was going to post to
that but I'm glad you found the typo and corrected it yourself<g>!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

Hi Garry,

Thanks for the follow up.
Glad i found the typo too, and as you write, Claus will have meant it
to place the point.
Personally i write a lot of typo's.

I didn't meant to reply twice, as you can see above i'm still not
familliar with the new site.
So i tried to post on both, not knowing that it would work on the new
site.
The text in the buttons (Dutch translation) is verry confusing too.

some questions:

1 ) Anyone any idea why the code in my first message is working as
long as the IsAddin property = False, but no longer when it's set to
True? It's verry confusing.
2) Anyone knowing where i can find good lecture abouth this kind of
'pitfalls', especially when creating Add-Ins.
- I know that it's impossible to add sheets in a Add-In at run time,
or you need to set the property IsAddin temporarely to false, this
works.
- I found out that it's also impossible to add a chart in a Add-In at
run time, but need to be alreddy placed in the Add-In at design time.
If i'm wrong, please correct me by telling me how to do so.
- You can't use ActiveWorkbook, but need to use ThisWorkbook.
- You can't use Select in a Add-In (or any hidden workbook).
- .....

Regards and once again thank you both for the replies.

Ludo

 

[GS]

I suspect that the 'Sort' requires the sheet be visible and active. Not
sure about that but I've always used a temp wkb to dump data for
sorting because it's faster than any VBA sort algorithms I've seen. I
do this from addins so I don't have to set the addin 'IsAddin=False'!
IMO, it's just not good to make changes to an addin at runtime. There
are exceptions where storing settings is concerned, but these are
usually temporary and changes aren't saved (as a rule). Not sure why
you use a sheet in the addin itself...

ThisWorkbook *always* refers to the project running the code.
ActiveWorkbook *always* refers to the current visible window.
You can't use 'Select' in hidden sheets, but then it's rarely necessary
to select anything anyway.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ludo]

Op maandag 21 mei 2012 15:27:44 UTC+2 schreef Ludo het volgende:

Hi all,

I'm trying to sort a column (column T) in a hidden page (Add In
property = True).
I found some code on the net, but i always get a Error 9 message
(Subscript out of range (Error 9).
When i set the IsAddin property to False, everything's right, but as
soon as i set it back to True, i'll get the error message.

What am i doing wrong?

Using win XP & Excel2003 SP3.

here's the code i use:

'sort
With ThisWorkbook.Sheets("common settings").Range("T1" )
.Cells.Sort Key1:=Sheets("common settings").Range("T1"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With

following 'version' of the code generates the same error:

'sort
With ThisWorkbook.Sheets("Common Settings").Range("T:T")
.Cells.Sort Key1:=Sheets("Common Settings").Range("T:T"),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers
End With


Any help welcome.

Regards,
Ludo

Hi Garry,

Thanks for your answer.

I wrote a VBA application ( no, not the one for the Dymo450 Label Printer -if you remember ) running under Excel and need to fill-out several templates. In this application i opened - copy - paste the templates into the application, but because the IsAddin property is set, i needed to set it temporarely to False.
At that time, i wasn't aware that you can't add sheets into a Add-In at runtime.
I'm not quite sure if i need to set the IsAddin property to False to changefrom printer, have to check the code.
So it's possible that i'm comming back with an other question on how to change from printer in a hidden workbook and print it out.

Now i'm extending this application, but i've learned a bit from this 'Add-In problem' so i don't add new pages into the Add-In, but keep them completely outside the Add-In. All the things i need in the Add-In are implemented in the Add-In at design time, so all the sheets i need and a graph.
Data that i need like common settings and unit specific data are stored in ..txt files that i load into the Add-In at run time.

As wtritten above, i hope that changing from printer doesn't give me problems with the Add-In. Fact here is that i need to print on white paper for the default production units and need to print on yellow paper for repaired units comming from customer service. Because VBA can't select a tray, and you can't define twice the same printer in windows, the first one with tray1 as default, the second one with tray2 as default, need i to print to 2 different printers.

Regards,
Ludo

 

[GS]

Ludo,
Basically speaking, Addins are apps designed for automating specific
tasks. They often make use of other 'app' workbooks as 'helper
templates', obviating any need to compromise the addin's 'IsAddin'
setting! If you don't want the extra burden of distributing multiple
files with your project then use a temp workbook 'on-the-fly' and
programmatically create your template for each use.

Essentially what you need to do is configure the printer
programmatically before each print job is sent. While this is not
readily possible with VBA's built-in features, it is doable using APIs
and standard VB. I don't have any experience with this but you could
get help from either of the groups listed in my signature.

If your printouts use a template then you can store this in a separate
'helper' workbook that you open 'behind-the-scenes' with ScreenUpdating
turned off.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion