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

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 Tutorials

Split Text into Multiple Cells in Excel
How to split text from one cell into multiple cells quickly and easily in Excel.  This includes how to split names, ...
View Different Parts of a Worksheet at Once in Excel
View different sections of the same worksheet when the sections are far apart.  This allows you to better work on ...
Print Selected Worksheets in Excel
This free excel macro will print all selected worksheets in Excel. If there are no other worksheets selected the a ...
Print The Current Worksheet in Excel
This free Excel macro will print the current active worksheet in Excel. This means that whatever sheet you are cur ...

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

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.

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.

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?

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?


Sub Rec()
AppActivate ("Form One")
SendKeys "{TAB}"
SendKeys "{9}"
End Sub

Hi Folks,

I am trying to create a string of code that takes values a user inputs onto the macro-based spreadsheet and inputs them into a window called "Form One"

AppActivate ("Form One")

this part of the code works great in that it activates the window "Form One"

The next part doesn't work at all. I am trying to use the "Tab" button to move the cursor to the next field and 'type' the number "9" but it isn't working at all. And by not working at all I mean that the macro selects the window "Form One" and then nothing happens.

The weird thing is, if I run the macro enough times (repeatedly hitting the "Start" box I have assigned the macro to), the Macro does tab over and type 9 into the next field.

Can someone please troubleshoot?


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.


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??


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?

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

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.


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.

Hi Forum,

Today I have a question about how to minimize a userform and the excel application window. I am currently using the following code to create a minimize button on my user form.

Please Login or Register  to view this content.

To create the button on the form i am using to following code:

Please Login or Register  to view this content.

This code allows me to minimize the form with the application window still visible. I can not minimize the application because the window is locked when the form is running. Is there away to link the minimize form button with the minimize the workbook button?



Hi All,

I have a question regarding userforms with excel. I have a form I created using the macro editor and I was wondering if there were a way to insert this form directly into a cell in my worksheet without having it appear as a separate window. I have it set up right now so that when I click on a button in the worksheet the form appears as a separate window but I'd like it to appear in cell A2 for user interaction.

Any Suggestions? Thanks in advance!

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!

Hi All,

I have a form that I display when the user click on a button. This form will display some info for the user to select and after the user selection is done, I bring up another form. The second form needs to take input from the active sheet , ie. user can click on any cell. The problem is, when I click on a cell while the form is displayed, the form goes back and excel is become active. I want the form to be displayed all the time. How can I achieve this?

There is a similar scenario in Excel 2007, if you use the Insert function menu, it will display the
function dialog and once you select a function, function argument dialog will be displayed. User could select any cell but the Function argument dialog will always remain in the front. I am trying to do the same.

thanks for the help.


(excel file attached)

I have come across one small error with my userform when running on Windows 7. The form works great on WinXP, but the PC that will own it runs Windows 7. I needed the form to stay open as the top window to everything else that is open, but the code I found for that seemed way over my head so I found the more simple "xlMinimized" function which allowed the userform to show in the toolbar but still keep the workbook hidden. I have users entering info on this form and will need it to stay open for possibly 1-2 hours before clicking the STOP TIME button before they save to Excel. When you open this file, the userform opens fine.... but when you click another program window and click the toolbar to show the userform again, the spreadsheet magically appears behind the userform (which I do not want). This does not happen on WinXP. Do you know what might be missing here ? Thank you in advance...



I'm using Excel 2002 on Window XP. I have managed to create a combined line - column chart on 2 axis as featured in the Chart Wizard - Custom Types folder. However, I would like to change the bar chart aspect of the chart to display each bar in a split format.

Currently, it displays one colour showing the total value of the bar. However, I would like each bar to be split into 4 different colours representing 4 different rows of data that make up the total value.

Here are the data values:

Month (x axis) Dec-05 Jan-06 Feb-06 Mar-06 Apr-06

Line 50% 50% 46% 49% 53%

Bar - split 1 0 0 0 0 0
Bar - split 2 0 0 0 0 0
Bar - split 3 0 0 0 0 0
Bar - split 4 0 0 0 0 0