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

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.

Brett

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

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.

Here's the Modeless Userform Code for Excel 97

This is shamelessly taken from:
http://www.bmsltd.co.uk/Excel/Default.htm

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 ModelessForm.zip.

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.

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.

thanks!

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.

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