Excel - Sort in hidden page crach

Hi all,

I am 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 is 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 is 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 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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

tom,
ottom, _

Thanks Claus for the fast reply.

But there is 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:=3D.Range("T1"), Order1:=3DxlAscending, _
'added a point (.) just before the word Range.
Header:=3DxlNo, OrderCustom:=3D1, Orientation:=3DxlTopToBottom, _
DataOption1:=3DxlSortTextAsNumbers
End With

Thanks again,
Ludo

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

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,
I am sure Claus meant for the period to be there. I was going to post to
that but I am 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

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 did not 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 =3D False, but no longer when it is set to
True? it is 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 is 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 is 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 cannot use ActiveWorkbook, but need to use ThisWorkbook.
- You cannot use Select in a Add-In (or any hidden workbook).
- .....

Regards and once again thank you both for the replies.

Ludo

I suspect that the 'Sort' requires the sheet be visible and active. Not
sure about that but I have always used a temp wkb to dump data for
sorting because it is faster than any VBA sort algorithms I have seen. I
do this from addins so I do not have to set the addin 'IsAddin=False'!
IMO, it is 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 are not 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 cannot use 'Select' in hidden sheets, but then it is 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

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

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 templ=
ates. In this application i opened - copy - paste the templates into the ap=
plication, but because the IsAddin property is set, i needed to set it temp=
orarely to False.
At that time, i was not aware that you cannot add sheets into a Add-In at run=
time.
I am not quite sure if i need to set the IsAddin property to False to change=
from printer, have to check the code.
So it is possible that i'm comming back with an other question on how to cha=
nge 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-I=
n problem' so i do not add new pages into the Add-In, but keep them complete=
ly 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 does not give me proble=
ms with the Add-In. Fact here is that i need to print on white paper for th=
e default production units and need to print on yellow paper for repaired u=
nits comming from customer service. Because VBA cannot select a tray, and yo=
u cannot 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 diff=
erent printers.
=20
Regards,
Ludo
=20

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 do not 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 do not 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