Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Macro To Run User Form, When Finished Active Range Changes Split Window

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have a split window in order to keep the top 3 cells at the top of the page for headings all the time. I have a userform which, once completed, returns the range and effects the split window. Is there any solution for this?

Also i like to use the tab button on my keyboard to manoeuvre around the form but it seems to be in a sporadic order, can i change this?

I am a novice with VB and Excel!!

Many thanks!!

-The form is run from the "qualify" sheet and then click on Qualify at the top.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics

Excel friends,

I have a sheet that have protected, workbook and sheet, and also I have previously Split the Window in two sections at row 30. If I open this workbook in other PC with an smaller screen the Split Window disappear to become a regular non-split window. in the same PC the Split shows up with no problem.

How can I make that the split window always be there regardless of the PC/Screen used?


Hi All,

I have excel file in which i have created one user form.And that excel sheet also contains Freeze Pane and Split window on which this userform is located.

i want to fix userform at one location in that frezze pane and split window.

please help me on this...

thank u


When I split the Window in a worksheet, my cursor moves the contents of both windows at the same time. I am trying to split the window so that I can position one side to show one thing and then move around the other side. Can this be done? Or do I need to do a Window/New thing. Just wondering if I can split it with a pane. Thank you.


Is there a work-around for safely closing an Excel window when there is a hidden UserForm in it?

I have a multipage UserForm with a "close" command button that hides the form. And if while that form is hidden the user clicks the Excel "Close Window" button (the small X on the toolbar under the big X that closes the entire program), Excel crashes. I guess Excel does not like to shut down an active window with a hidden UserForm in it.

Is there a way around this...other than simply not using the "hide" in VBA?


Hello, On MS-Word (2003),
1. I'm building a macro that uses a User Form to interact with the user (several times), asking the user to select text (or a line) and then return to the Form and hit OK. This goes back & forth between the Form and the Doc several times.
2. Everytime the user returns back to the Doc to select the next batch of text or line, he/she has to click on the Doc window 1-time to give it the FOCUS, then click again to make the text or line selection.
3. Sometimes this is confusing. They may click only 1-time and think they made a line selection, but they really didn't (they only made the initial FOCUS change, and didn't really make the intended range selection).
4. Question: Within the VBA code, is it possible to automatically move the FOCUS from the User Form to the Doc Window for the user? So, when the user clicks on the Doc the 1st time, he/she is selecting text (line) on the 1st mouse selection.
I can't find any way to move FOCUS from a User Form (that was just shown) back to the Document (and away from the User Form). Do you have any ideas on how to do this???

Figured My Own Solution: Use following SendKeys code (see below). This sends focus back to the main Word Window. Found this key combination in the exhaustive list of HotKey Tips from MS Help. Tested and works even if other Word docs are open or if other application programs are running at the same time.

  Form_x1_XRef.Show               'Show X-Ref UserForm Prompt
  SendKeys "%(+{F6})", True       'Same as "ALT+SHIFT+F6", returns focus to Doc

How can a window be split both horizontally and vertically on the same worksheet.

I actually got this to work somehow by stumbling into it, but I don't know how to reproduce it.

Window/Split only seems to allow one direction, as it grays out after one direction is done.

When a worksheet window is split and a comment shape overlaps the split bar (with or without Freeze Panes), the comment appears behind the split bar instead of in front of it. Is there a way to have comments always in front of window split bars?

I have a userform I have constructed and I have the VBA code written for it but I was wondering if it is possible to make it so when the form pops up to make the excel window minimize and make the user form stay, and when I hit the button on my user form after entering my data into the form the excel window to maximize back.
I have my userform macro assign to a button in my excel sheet.

Hello All,

I have an user form such that multiple users should use that form,
Also, the userform should be protected. I have protected the sheet through

The problem is when the workbook is unshared and protected then everything goes fine,. But when i share the protected workbook i am not able to run the form.
After entering all the details in the form and when i click the submit button,
run time error message "unprotect sheet method class failed" appears in the window.

Is there any solution. please tell me.


I have a user form that uses scroll bars. I added a button at the bottom of the form to print what is on the user form. The code executed is userform.Printform. The only part of the form that prints is the part of the form that is in the user window. How can I get the whole form to print, including what is out of the user window?


I created a form box in Excel 2007 using an online code. What I would like to do is open my workbook and have the form appear via a macro. I can only open the form by opening the VBA window then click on the Run Sub/ User Form arrow. When I click that icon my workbook opens with the form and I can then enter data. Could you please advise how to make the form appear on my worksheet without having to open the VBA window? I thank you in advance for your help!

Is it possible to split a userform entry between 2 cells?

I have a userform where there is a comment text field. If the comment is more than 300 characters long I would like to put the first 300 in Sheet("Request Form").Range("descpt1") and the rest in Sheet("Continuation").Range("descpt2"). I would perfer not to split actual word. Can this be done?

Thank you for your time and help.

Is there a way when you split your window to scroll vertically only on one side of the split and not the other?


Every time I Open my userform in access (by clicking on the forms shorcut) the window opens and the userform appears in a no quiet maximised state. I have to click on the maximise symbol on the top right corner to make it fit the window.

Is there any setting or property which will automatically maximise the form to the size of the window. I can use a macro which works fine though there must be a standard way of doing this.


I've never worked with User Forms before, and was wondering if there was a way to dim or hide or grey out some text boxes and item names unless the user chooses an option B.

What I'm doing is allowing a field for money to be put in. most of the time 100% of the money will go into that account, but sometimes the money will be split among other accounts. When this happens I'd like the user to be able to check a button that says "money split into other accounts" that would automatically allow the user to manually split the money into up to 4 other accounts that go from being inactive and unchangeable to active and editable.

This seems like it's something that a user form can do, if so can someone tell me how or point me to the answer?


Does anyone know the procedure for viewing a selected range of cells as a separate window on a worksheet. Similar to "split" window but the selected range is a floating "window". I recall seeing this feature explained in another forum or MVP site but cannot remember now.

Hi All,

I have a macro that will format project files based on their project number. in order to record the prject number. I have created a form for the user to enter the project number. My problem is entering a command line to open the form once the user selects which type of project file is to be formatted (a popup window first displays in the macro). I would like for the user form to popup if the correct button is clicked on the original popup window. How??

I have an order form that I create but it is too lengthy when it is printed out. Everything on the list prints out including the items not ordered. I want to be able to do the following. This order form contains warehouse supplies used for shipping purposes. There are over 50 items on this list however we do not always order everything on this list so if I can create a formula that will only display the item ordered when a value is entered.

Description PRODUCT CODE/On Hand Inventory/Inventory/Qty to Order
1/2" METAL STRAPS (ROLLS) 4 10 6

I would like to create an "OK" command button. If a value is entered in the onhand inventory column I would click "OK" and this form will open in another window only displaying that items that was selected. Also I would like to have an option to print.

Can anyone help???


Have tried and failed to find an answer for this. I have created a userform for a user to use on a spreadsheet and I can't work out how to make the userform appear in the centre of the active Excel window it is being run for. My screen set up are three monitors, from left to right: 2 full size and one laptop with the VBA coding window maximised in the middle and the Excel window on the left.

Currently when "UserForm1.Show" is executed, the form appears to the very left of the middle (VBA coding) screen and approximately half way down from the top. I'm believe I need to modify the properties of Userform1 but the only options can see to change are Left and Top in its current state are both set to 0 (despite the form appearing as described).

My aim is to have it appear in the very middle of the Excel window on the left.

Thoughts/suggestions please?

Hi, I am wanting to create a spreadsheet for my stationary orders with some macro's and I have no idea on how to set this up. All the product information will be in Sheet 1 and the Order Form will be in Sheet 2 of the document.

In "sheet 1" we will make a list of all items that get ordered on a regular basis. This can be up to 500 items but
I have given you an example of 6 to play with.

We need the "QTY", "Unit Price" & "Total" in both "Sheet 1" and "Sheet 2" to sum up with a formula

Once the user has chosen the line item they will click on the "add" button next to the line item.

This will inturn take that particular line item and insert only the "Code", "Product", "Unit Price" & "Total" columns in "Sheet 2"
and leaving out the "Page Number", "Item Number" & "Description" & "QTY" fields to Sheet 2 (Order Form)
The user will manually change the "QTY" field on the "Sheet 2" (Order Form)

When the user selects another product and clicks "add" then this new line item will be added below the first line item
on Sheet2 (Order form) and so on.

Please have a look at my example which I have included In this email.

We will probably only be ordering 10 - 50 items each time so we wont need a huge list on the order form (Sheet 2)

Many thanks in advance everyone


I am currently using the following code to open an Internet Explorer window, so that I can fill out and submit an online form.

Dim IE As InternetExplorer
Set IE = New InternetExplorer

With IE
.Visible = True
.Navigate "URL"
End With

However, when I go to the page in question a pop-up window appears. I therefore need to be able to close the pop-up window, or make the original (parent) window active, before I can submit the form. Does anyone know how I can do this?

Any help much appreciated - Thanks


I'm looking to split the window in the spreadsheet so that the options on the right stay static and the scrollbar to the immediate left of them is the only bar used to scroll up and down the data to keep everything on one screen. Is this possible? Here is a picture of the spreadsheet so far which my explain what i'm trying to do...

spreadsheet.JPG (Had to cut picture down a lot so it would upload)

If so, how would I go about doing this? I've tried splitting the window with v.little success. Any help would be appreciated.




I have simply recorded a macro using "solver". I have set objective cell, variables and constraints.
Then I created a "form control button" in my active sheet and have assigned this macro to this button.

When i run the macro via this button, at each run it doubles the constraints in solver. I see it when I am checking the solver set after each run. After several runs, macro returns me with error:

The problems is too large for Solver to handle
Solver is limited to 200 variable cells and 100 constraints, plus bounds on the variable cells.

How can I prevent macro to increase the number of constraints in solver ?

The second problem is that, each time when I run macro with form control button, excel opens a Solver Result message window after solution.
How to prevent it ?

Thanks for answers.

Is there any way to set up a view such that I am viewing two parts of the same sheet side by side, but I am free to scroll up and down independently on either side?

I have tried the standard "split window" and "freeze cells" approaches in the Window menu.

Both of these essentially de-couple left-right scrolling for different segments of the screen, but up-down scrolling is still linked for both sides of the freeze or split.

First I'd like to thank everyone who's helped me along in build my current spreadsheet. The latest bundle of fun change involves trying to change my sheet around so the buttons that the participant interact with all be displayed in a nice pop-out window because the participants "aren't supposed to see anything else". I've been messing around with creating a pop-up window but had no luck whatsoever.

All I really need it do to is be able to click the "Begin SSQ" button and have cells A10:G45 appear in a separate window. Once the user makes their selections, then can click "Click to Finish" and it returns back to the spreadsheet.

As long as the button selections made in the popup window remain on the spreadsheet I can process all the data from there. Haven't been able to find a good way to make it work so for now I have a Hide and Unhide macro set up to hide the stuff their not supposed to see. Everyone just feels it'd be better if participants weren't looking at the excels cells while filling in their survey.

I've uploaded the file for anyone that wants to take a look. Thanks again all!