Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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: 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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
On 21 mei, 15:57, Claus Busch wrote:
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
On May 21, 7:50*pm, GS wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 run time. I'm not quite sure if i need to set the IsAddin property to False to change from 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort in hidden page crach
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Are Hidden Rows/ Columns Included in a Sort? | Excel Discussion (Misc queries) | |||
Page Breaks and hidden columns | Excel Discussion (Misc queries) | |||
how can i fix a spreadsheet that didn't sort hidden columns? | Excel Discussion (Misc queries) | |||
Sort Pivot Table using hidden data field | Excel Discussion (Misc queries) | |||
Sort a Range in a Hidden sheet | Excel Programming |