userform + spredsheet

Posted by jpickering on April 20, 2001 11:25 AM

Is there a way to switch focus from a userform back to the currently open spreadsheet? I am trying to do some fancy editing of cells, and would like to leave the userform I've created for editing open as I look around the sheet.... The currently selected cell is used in the userform's functions as the start to do some updating.

Posted by Dave Hawley on April 20, 2001 11:37 AM

Hi jpickering

you can do this in Excel 2000 only by setting the "ShowModal" property in the Properties window.

Can't be done in Excel 97, the best you can do is Hide the Form and have it re-show.

Dave

OzGrid Business Applications

Posted by jp on April 20, 2001 12:43 PM

'-----------------------------
THANKS! You pretty much backed up what I was afraid of... :(

Posted by Jerid on April 20, 2001 12:46 PM

Here is some code I came across that will make a User Form in Excel97 Modeless. I haven't used it in anything yet, but I have seen it work.

'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 lHWnd As Long
Dim bDragDrop As Boolean

Private Sub UserForm_Activate()
'Find the Excel main window
lHWnd = FindWindowA("XLMAIN", Application.Caption)

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

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

Private Sub CloseForm_Click()
EnableWindow mlHWnd, 0
Application.CellDragAndDrop = bDragDrop
Unload Me
End Sub

Posted by Dave Hawley on April 20, 2001 12:51 PM

You may be able to use the "Refedit" control? But it depends on what you want to do. If you don't have this control on your Toolbox right click on it and select "Additional Controls"

DaveOzGrid Business Applications

Posted by jp on April 20, 2001 12:58 PM

Hey thanks for the thought. I will take a look when I can... Thanks again :)