variant array subscript error

[jindon]

Hi

strTempAry =  Worksheets("PrintList"). Range("H1:H1000") 
makes strTempAry(1 To 1000, 1 To 1) 
 ' i.e.
strTempAry(1,1)=H1 
strTempAry(2,1)=H2 
strTempAry(3,1)=H3 
 
strTempAry(1000,1)=H1000 

when you substitute Range value in an array this way, element always begin with 1 and multidimentional even it is one column or one row therefore, if you need to refer the elements it should be

strTempAry(x,1) 

rgds,
jindon

[jeff rogers]

Hi dr,

x is not set to "0".

Sorry for the confusion with all these 1s and 0s...

At the beginning, nothing has been manually selected to print (one worksheet will be open).

the last worksheet is sheet 812. x is equal to 812.

the "PrintList" array (strTempAry) setting for worksheet 812 is equal to zero and therefore should not print but does anyway.

... because as far as I can tell ... I have not told XL to print anything by way of manually selecting a worksheet and a print range, I just selected the print icon. Iinstead I am telling XL what to print via VBA, so I think when XL gets to the "end" of the VBA code XL "wants" to print something because the print icon was selected and "XL" itself did not print anything ... so ...the only thing XL knows to print would be the last activated worksheet. And that is what XL does.

My question is: how do I tell XL that the procedure is selecting what prints and that XL should suppress its own printing needs.

Is this helping?

Back before coding got "simpler" I used to have to program separate code to handle EOF/Last Record processing. This is what that is like - I feel like I need to tell XL that when the procedure is done that XL's "need" to print is done.

Thanks for sticking with me ...

Jeff

[jeff rogers]

Hi Jindon,

Thanks for clarifying ... your explanation and visuals were excellent!

Jeff

[rbrhodes]

Hi Jeff,

Since I can't find a way (yet) to convince XL to ignore the Print click I'm sending a custom button approach. The new button will run the macro only.

Cut the Before Print macro and paste it into a module and rename it. I used:

Sub pmacro()

in this example.

Run the AddPrint sub. This will add asecond Print Icon (FaceId = 4) on the Standard Menu bar in position 5 with a 'ToolTip' of "Print with Macro". If having two Icons the same is acceptable then put AddPrint and DelPrint in the WorkbookOpen/Close events. If not...then:

Click View, Toolbars, Customize, New to add a new toolbar named "Custom 1". (Note if you already have custom toolbars, name the new one and remember the name for the AddBluePrint & DelBluePrint subs),

Now drag the Second Print Icon to the Custom 1 toolbar. Right click and Edit Button Face to change it's look (I changed the backgroud to blue on mine). Close when done.

Now run DelPrint sub to clear the second Icon from the menu bar. Run AddBluePrint to add the new Icon to the menu bar and DelBluePrint to remove it. If all works well, put AddBluePrint and DelBluePrint in the Open/Close events and it will be there for thisworkbook ONLY.

It's the only method I could find for saving a custom face ID - storing it on a custom (hidden) toolbar.

Cool info on the Arrays jindon thanks!

Learning lots about custom menus, xlDialogs, etc!

Are we having fun yet...? <G>

Cheers,

dr

Sub AddBluePrint() 
     
     'Moves custom face button to Standard menu bar, position 5
     
    Application.CommandBars("Custom 1").Visible = True 
    Application.CommandBars("Custom 1").Controls(1).Move Bar:=Application. _ 
    CommandBars("Standard"), Before:=5 
    Application.CommandBars("Custom 1").Visible = False 
End Sub 
Sub DelBluePrint() 
     
     'Moves custom face button from Standard menu bar to Custom 1 for storage (saved)
     
    Application.CommandBars("Custom 1").Visible = True 
    Application.CommandBars("Standard").Controls(5).Move Bar:=Application. _ 
    CommandBars("Custom 1") 
    Application.CommandBars("Custom 1").Visible = False 
End Sub 
 
 
Sub AddPrint() 
     
     'Adds second PrintIcon to Standard menu bar w/new caption and action
     
    Dim NewToolbar As CommandBar 
    Dim NewButton As CommandBarButton 
    Set NewToolbar = Application.CommandBars("Standard") 
    Set NewButton = NewToolbar.Controls.Add _ 
    (Type:=msoControlButton, Id:=2950, Before:=5) 
    NewButton.FaceId = 4 
    NewButton.Caption = "Print with Macro" 
    NewButton.OnAction = "pmacro" 
End Sub 
Sub DelPrint() 
     
     'Removes second PrintIcon from Standard menu bar
     
    Application.CommandBars("Standard").Controls.Item(5).Delete 
End Sub 
 
Sub r() 
     
     'And just in  case
     
    Application.CommandBars("Standard").Reset 
End Sub 
 

[jeff rogers]

Hi dr!

WOW! THANKS! I will implement your solution within the next few days.

Jeff

[rbrhodes]

Hey,

you're welcome! (It was quite fun)

Let me know if there's any problems or questions...

Cheers,
dr