Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba user form

ResolvedVersion 2003

Kamil has attended:
Excel VBA Intro Intermediate course

VBA User Form

Hello,

I have got a question regarding ending user form. I was trying to build simple form to set print area but I can't remove form after formatting is performed. I was trying to use "Unload" function, but in this case doesn't work (below my code)

Private Sub CommandButton1_Click()

Call TextBox1_Change
Call HorizontalLayout_Click
Call VerticalLayout_Click
Unload CommandButton1_Click - this line doesn't work

End Sub

Private Sub HorizontalLayout_Click()
If HorizontalLayout = True Then
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
End Sub

Sub VerticalLayout_Click()
If VerticalLayout = True Then
ActiveSheet.PageSetup.Orientation = xlPortrait
Else
ActiveSheet.PageSetup.Orientation = xlLandscape
End If
End Sub

Private Sub TextBox1_Change()
Dim x As Integer
x = TextBox1.Value
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = x
End With
End Sub

RE: VBA User Form

Hi Kamil

Thanks for your post and the code as well. We may be able to solve this for you but will need to have your working files to run the code in.

If you can email to jacob@stl-training.co.uk we will review and should a quick fix reveal itself we will let you know.

Also if the requirement takes more time to figure out and we need to allocate a trainer to work on it we will advise of this and any related costs for you to decide.

Kind regards

Jacob

Fri 5 Nov 2010: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Changing Excel file and worksheet defaults

The appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt.

By opening, modifying and saving these templates you can change the default settings for all new files and/or all newly inserted sheets.

Use Book.xlt to make change to defaults for new workbooks; and Sheet.xlt to change defaults for sheets.

If you can't find either of these files on your computer, you can create and save them yourself.

You can do this simply by creating a new workbook with the setting you want to use as your defaults; then saving them with the appropriate name in the XLStart folder. If you are using Office 2003, this is usually found in C:\Program Files\Microsoft Office\Office11\XLStart.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.