Hide name box and activate first page of multipage control

[MSingh]

Hi Hans, Thanks for that guidance.

Plase advise:
A.
I have hidden the ribbon in excel 2007, can the left top section where the named ranges appear also be hidden? or if not then can the size be reduced such that the drop down list cannot be accessed?

B.
In my application, i use a 3 step wizard to restore an archived batch (so that the user could make corrections to errors which only become known at the time he is importing into an accounting application).

However:

1. i cannot get page1 to always be the default page when the wizard opens, must i load my code for your correction?
code is from http://msdn.microsoft.com/en-us/library/aa192538(office.11,printer" onclick="window.open(this.href);return false;).aspx
2. i also need to prevent the user from exiting the wizard by the "X" that appears on the box right-hand top corner or by esc so that he only uses the "cancel" or "finish" cmd buttons.

Thanks
Mohamed



Thanks again
Mohamed

[HansV]

A. I think that you mean the address box in the formula bar. You can't hide just this box, but you can hide the formula bar as a whole: clear the check box "Formula Bar" in the View tab of the ribbon.

x182.png

You can also do this using code:

Application.DisplayFormulaBar = False

Please note that this is a user-level setting. If you hide the formula bar, it will be hidden in all workbooks. Users won't appreciate that, so you should take care to restore the formula bar when your workbook is not active.

B. Your link doesn't work, I think it should have been Chapter 20: Creating Advanced User Forms.

B1. A userform with a MultiPage control will display the page that was active when you last saved the userform (or that was active in the Visual Basic Editor if the form is loaded there). To get around is, activate the first page in the UserForm_Initialize event in the form module:

Code: Select all

Private Sub UserForm_Initialize()
  Me.MultiPage1.Value = 0
End Sub

Substitute the correct name for the multipage control if necessary. The Value of the multipage control is the zero-based index of the active page, i.e. 0 corresponds to the first page, 1 corresponds to the second page, etc.

B2. You can use the userform's QueryClose event for this:

Code: Select all

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
  End If
End Sub

You MUST have some code that unloads the userform, for example in the Click event of a command button, otherwise the user has no way to exit the userform any more.

[MSingh]

Hi Hans,

Thanks Again for all your solutions.

My apologies for the incorrect link, you ref was correct.

Yes, the useform does have an unload cmd.

The application is meant to be compiled within an Excel compiler converting to exe file & specific compiler would not interfere with end-users normal excel formula bar.

The wizard works perfectly now, thank you very much.

Kind Regards
Mohamed