MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I want my form to be visible, but allow access to sheet

Posted by Melanie Swarner on November 05, 2001 5:58 AM

I have a form that I want to show over a sheet. I want to have full access to the form and the sheet. Is this possible. The form steps the user through a process and reacts to what they've done to tell them what to do next. I don't want them to have to close the form to perform a step and then reopen it.

Thanks! melanie

Posted by Dank on November 05, 2001 6:08 AM

Good Morning.

I am trying to move a series of word documents into Excel. When I paste from Word into Excel, it puts the word text into a number of different cells. For instance, if I have:

1. Go outside.
2. Look around.

excel will put this into different cells (probably because of carriage returns) when I need these to be in the same cell (because of the type of spreadsheet I am creating). Is there a macro or anyway to combine these lines back into one cell or to prevent Excel from doing this when I paste fromWord? I have hundreds of these that I would rather not do by hand.

Thanks again.


P.S. It doesnt matter to me if it ends up looking like 1. Go outside. 2. Look around I just needit in one cell

Posted by Mark O'Brien on November 05, 2001 6:27 AM

Melanie, there are two ways to do this. It depends if you are using Excel 97, Excel 2000 or greater.

Microsoft addressed this problem in Excel 2000 and I assume that it is still the same in XP. When you show your userform use the "vbModeless" command. e.g.

UserForm1.Show vbModeless

This will allow the user to click in cells and menus etc. And it's very nifty.

For Excel 97, well, that's a problem. Fortunately there is a solution. There's a quick way, which I don't like, and a slightly more lengthy way.

If you are using Excel 97, please repost. I don't have the code at hand, but I can quickly get it sorted.

Posted by Mark O'Brien on November 05, 2001 6:39 AM

Here's the Modeless Userform Code for Excel 97

This is shamelessly taken from:

This guy contributed to the best Excel VBA book I used. Well, most useful one anyway since it got me into using Windows API's.

There are some great demos on this page and this code is taken from

Basically, on your userform put this code in. It should be noted that the "Private Declare" statements are in the (Declarations) section and not in any subroutine.

'API function to enable/disable the Excel Window
Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function EnableWindow Lib "user32" (ByVal hWnd As Long, ByVal bEnable As Long) As Long

Dim mlHWnd As Long, mbModal As Boolean, mbDragDrop As Boolean

Private Sub UserForm_Activate()

On Error Resume Next

'Find the Excel main window
mlHWnd = FindWindowA("XLMAIN", Application.Caption)

'Enable the Window - makes the userform modeless
EnableWindow mlHWnd, 1

'Set the button caption and remember it's modeless
btnModal.Caption = "Go Modal"
mbModal = True

'Disable Cell drag/drop, as it causes Excel 97 to GPF
mbDragDrop = Application.CellDragAndDrop
Application.CellDragAndDrop = False

End Sub

Whatever code you use to kill the form e.g. "Unload Me", put this code along with it:

Application.CellDragAndDrop = mbDragDrop

The reason for this is that cell drag and drop is disabled because it causes a general protection fault using this code. (and you'll eventually get some user complaining that they don't have a wee box to "copy stuff" from cell to cell.)

Access to the dropdown menus is slightly limited, but is still possible. When you click on a menu for the first time, you need to double click. This will bring up the "Customize" dialogue box. Get rid of this and the menus will now function as normal.

Hope this is helpful. Repost if you have any problems.

Posted by Melanie Swarner on November 06, 2001 9:50 AM

Worked - Can I record a macro to get around the double clicking of the menu?

This worked great. Is there a way to record a macro that double clicks the menu and clicks close so that it's seemless? I tried recording going to Tools-->Customize and clicking close, but nothing recorded in the macro. Even the way it is, I can live with it.


Posted by Mark O'Brien on November 06, 2001 7:52 PM

Re: Worked - Can I record a macro to get around the double clicking of the menu?

The macro recorder won't work to record moving the mouse to double click etc, but you could do it using some more API calls. Unfortunately, this may freak the hell out of the user, so I wouldn't really recommend trying it.

Posted by Melanie Swarner on November 07, 2001 8:27 AM

I see you've met my users..... :-) (nt)