MikeGozna
New Member
- Joined
- Feb 19, 2019
- Messages
- 10
- Platform
- Windows
Hi Guys and good morning from the UK. I am a complete novice at vba and really appreciate the help this forum has given me on this very slow learning curve. Anyway on to the problem.
I am making a largish workbook which is basically a job estimating assistant. All is going well, but I am stuck on this bit.
I have created an array MyArray which appears in a listbox ,(MeListBox1.List) on a user form. The items in the listbox, Array are all sheets in the same workbook.I can select any or all of the sheets in the Array by a series of check boxes
on the userform and send them all to PrintPreview. The problem is that the same sheet may be required to be Portrait or landscape at print time. Also I may have to hide or unhide certain columns at print time.
The main question is how to alter the orientation and or hide/unhide columns at print preview time.
Also I dont think the Listbox to Array is working properly, so a completely new approach may be required.
I am sorry if this sounds a bit garbled, but any help would be gratefully recieved, but please try to keep it as simply as possible and if possible, explain the code to put this right, based on my novice status.
I am making a largish workbook which is basically a job estimating assistant. All is going well, but I am stuck on this bit.
I have created an array MyArray which appears in a listbox ,(MeListBox1.List) on a user form. The items in the listbox, Array are all sheets in the same workbook.I can select any or all of the sheets in the Array by a series of check boxes
on the userform and send them all to PrintPreview. The problem is that the same sheet may be required to be Portrait or landscape at print time. Also I may have to hide or unhide certain columns at print time.
The main question is how to alter the orientation and or hide/unhide columns at print preview time.
Also I dont think the Listbox to Array is working properly, so a completely new approach may be required.
I am sorry if this sounds a bit garbled, but any help would be gratefully recieved, but please try to keep it as simply as possible and if possible, explain the code to put this right, based on my novice status.
VBA Code:
Private Sub CommandButton2_Click()
'''''''''''''''''''''''''''''''''''''''''''
' PRINT PREVIEW OF SITE SHEETS
''''''''''''''''''''''''''''''''''''''''''
On Error GoTo ErrorHandler
Unload Me
Dim i As Integer
Dim cnt As Integer
Dim MyArray()
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
ReDim Preserve MyArray(cnt)
MyArray(cnt) = Me.ListBox1.List(i)
cnt = cnt + 1
End If
Next
'''''''[COLOR=rgb(41, 105, 176)](Somwhere in here I want to change the orientation of the printed sheet and be able to hide or unhide columns[/COLOR].'''''
''''''''[COLOR=rgb(41, 105, 176)]or maybe it should be coded before it gets to this point, Please help).[/COLOR]'''''
Sheets(MyArray()).PrintPreview
Exit Sub
ErrorHandler:
MsgBox "Please Select Sheets To Print"
PrintOptions.Show
End Sub