Also, it only does this in one direction. I have several userforms tied
together and the useform in question is like 3rd in a series of like 7
userforms. They all have continue and back buttons. This userform loads
fine and doesn't fire the continue button when opening it from the continue
button of the 2nd userform but does it when backing up to it from the back
button of the 4th userform.
--
Thanks
Shawn
Shawn said:
I have a userform with the code below. You can see I have "shut off" part of
the code. When the userform activates it is supposed to enable a continue
button if certain conditions are met. However, when it activates the command
button it also fires it (the code in the command button closes the userform
and opens the next userform). This is so strange???
Public Sub UserForm_Activate()
Set WSCal = Sheets("Cal")
WSCal.Range("HealthAnswerRange,HealthCmt2").ClearContents
With usfHealth
.cmbCt.Enabled = False
' If .chbxKid = True Or .chbxHeart = True Or .chbxEye = True Or
.chbxA1C = True Or _
' .chbxStroke = True Or .chbxAmputation = True Or .chbxFoot = True Or
.chbxGlucose = True Or _
' .optbutNone = True Then .cmbCt = True
' .lblHealth.Caption = WSCal.Range("HealthQ")
End With
Application.ScreenUpdating = False
End Sub
Hello Shawn,
Setting a Command Button's value to true fires the
CommandButton_Click() event. You may want to use a different control
or modify your forms to behave like individual windows that can be
minimized and restored. The other advantages would be that the user
could work on the worksheet while the form is displayed , or just work
with the form.
Here is the code. After adding a Standard VBA Module to your project,
copy and paste the code below into it. To make the form behave like a
window, first set the UserForm Modal property to False. This will
allow you use Excel while the form is displayed. Next add these 2
lines of code to the UserForm_Activate event code...
AddMinBox()
AddMaxBox()
'Begin Macro Code...
'///////////////////////////////////////'
'/ /'
'/ API calls to add the Minimize and /'
'/ Restore buttons to the Title Bar /'
'/ of a VBA UserForm. /'
'/ /'
'///////////////////////////////////////'
'
'Written: Jan. 30, 2007
'Author: Leith Ross
'Constants for ShowWindow (nCmdShow)
Const SW_HIDDEN As Long = 0
Const SW_NORMAL As Long = 1
Const SW_MINIMIZED As Long = 2
Const SW_MAXIMIZED As Long = 3
Const SW_NOTACTIVE As Long = 4
Const SW_UNHIDDEN As Long = 5
Const SW_MINWITHFOCUS As Long = 6
Const SW_MINNOTACTIVE As Long = 7
Const SW_RESTORE As Long = 9
'Constants for GetWindow
Const GW_HWNDFIRST As Long = &H0
Const GW_HWNDLAST As Long = &H1
Const GW_HWNDNEXT As Long = &H2
Const GW_HWNDPREV As Long = &H3
Const GW_OWNER As Long = &H4
Const GW_CHILD As Long = &H5
'Window Style constants
Const WS_DISABLE As Long = 0
Const WS_MAXIMIZEBOX As Long = &H10000
Const WS_MINIMIZEBOX As Long = &H20000
Const WS_THICKFRAME As Long = &H40000 'Style to add a sizable
frame
Const WS_SYSMENU As Long = &H80000
Const WS_ENABLE As Long = &HFFFFFFFF
'Get Window Long constants
Const GWL_HINSTANCE As Long = (-6)
Const GWL_HWNDPARENT As Long = (-8)
Const GWL_ID As Long = (-12)
Const GWL_STYLE As Long = (-16)
Const GWL_EXSTYLE As Long = (-20)
Private Declare Function GetWindowLong _
Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hWnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hWnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
'Function to Change how Window is Displayed
Private Declare Function ShowWindow _
Lib "user32.dll" _
(ByVal hWnd As Long, _
ByVal nCmdShow As Long) As Long
'Returns the Window Handle of the Active Window
Public Declare Function GetActiveWindow _
Lib "user32.dll" () As Long
'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar _
Lib "user32.dll" _
(ByVal hWnd As Long) As Long
Public Sub MinimizeWindow(Optional ByVal Window_Handle As Long,
Optional ByVal With_Focus As Boolean)
Dim RetVal
If With_Focus = True Then
RetVal = ShowWindow(Window_Handle, SW_MINWITHFOCUS)
Else
RetVal = ShowWindow(Window_Handle, SW_MINNOTACTIVE)
End If
End Sub
Public Sub RestoreWindow(Optional ByVal Window_Handle As Long)
Dim RetVal
RetVal = ShowWindow(Window_Handle, SW_NORMAL)
End Sub
Public Sub AddMinBox(Optional Window_Handle As Long)
Dim hWnd As Long
Dim BitMask As Long
Dim WindowStyle As Long
If Window_Handle = 0 Then
hWnd = GetActiveWindow()
Else
hWnd = Window_Handle
End If
WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
BitMask = WindowStyle Or WS_MINIMIZEBOX
Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
Call DrawMenuBar(hWnd)
End Sub
Public Sub AddMaxBox(Optional Window_Handle As Long)
Dim hWnd As Long
Dim BitMask As Long
Dim WindowStyle As Long
If Window_Handle = 0 Then
hWnd = GetActiveWindow()
Else
hWnd = Window_Handle
End If
WindowStyle = GetWindowLong(hWnd, GWL_STYLE)
BitMask = WindowStyle Or WS_MAXIMIZEBOX
Call SetWindowLong(hWnd, GWL_STYLE, BitMask)
Call DrawMenuBar(hWnd)
End Sub
'End Macro Code....
Sincerely,
Leith Ross
