PDA

View Full Version : Excel Forms Question



Peter Stahl
01-12-2010, 9:58 PM
I have a Userform I made in excel and when I click the OK button it doesn't exit and close the form. Any idea what I'm doing wrong? The form is just 3 optionbuttons and a command button for OK and another for Cancel.

thanks for any help, Pete


Private Sub UserForm_Initialize()

Opt_190C2160G = True

End Sub


Private Sub CMD_Cancel_Click()
'Unload Me
End Sub

Private Sub CMD_OK_Click()
Opt_190C2160G = True

If Opt_190C2160G = True Then _
ActiveCell.Offset(0, 4).Value = "190/2160"
If Opt_125C325G_ChartA = True Then _
ActiveCell.Offset(0, 4).Value = "Chart A"
If Opt_125C325G_ChartB = True Then _
ActiveCell.Offset(0, 4).Value = "Chart B"


End Sub

Doug Shepard
01-12-2010, 10:30 PM
not sure why it'sot closing but this routine sure looks puzzling

Private Sub CMD_OK_Click()
Opt_190C2160G = True

If Opt_190C2160G = True Then _ why test for this? You just set it to True ??
ActiveCell.Offset(0, 4).Value = "190/2160"
If Opt_125C325G_ChartA = True Then _
ActiveCell.Offset(0, 4).Value = "Chart A"
If Opt_125C325G_ChartB = True Then _
ActiveCell.Offset(0, 4).Value = "Chart B"

You also should make these Else-If statements. You're forcing the code to test the other options even if it already found the one that was set. Or aren't these radio button types where more than one can be selected?


End Sub

Peter Stahl
01-12-2010, 10:54 PM
Doug, Here's what I have for the form below.

Private Sub UserForm_Initialize()

Opt_190C2160G = True

End Sub

Private Sub CMD_Cancel_Click()
Unload Me
End Sub

Sub CMD_OK_Click()


If Opt_190C2160G = True Then _
ActiveCell.Offset(0, 4).Value = "190/2160"
If Opt_125C325G_ChartA = True Then _
ActiveCell.Offset(0, 4).Value = "Chart A"
If Opt_125C325G_ChartB = True Then
ActiveCell.Offset(0, 4).Value = "Chart B"
WhichTestIsIT = True
MsgBox WhichTestIsIT
End If




End Sub

Doug Shepard
01-13-2010, 5:25 AM
Try changing this:
If Opt_190C2160G = True Then _
ActiveCell.Offset(0, 4).Value = "190/2160"
If Opt_125C325G_ChartA = True Then _
ActiveCell.Offset(0, 4).Value = "Chart A"
If Opt_125C325G_ChartB = True Then
ActiveCell.Offset(0, 4).Value = "Chart B"
WhichTestIsIT = True
MsgBox WhichTestIsIT
End If

To this:
If Opt_190C2160G = True Then _
ActiveCell.Offset(0, 4).Value = "190/2160"
Else If Opt_125C325G_ChartA = True Then _
ActiveCell.Offset(0, 4).Value = "Chart A"
Else If Opt_125C325G_ChartB = True Then
ActiveCell.Offset(0, 4).Value = "Chart B"
End If
WhichTestIsIT = True
MsgBox WhichTestIsIT

May or may not do anything for the closing issue but it should only be possible for one of the 3 options to be selected at a time. Adding the Else statements lets the code bail out of the testing as soon as it finds the selected option instead of doing more tests once it's been found. I think the last 2 lines inside of the Endif block may be the closing problem but not sure. It's been a couple years since I've done much with Excel dialog boxes.

Peter Stahl
01-13-2010, 8:56 AM
Thanks for the replies Doug. Didn't seem to make a change but there are only 3 buttons.

Doug Shepard
01-13-2010, 10:45 AM
Do you need this statement at the end of the subroutine to get it to close?
Unload Me

Matt Meiser
01-13-2010, 10:59 AM
I'm far from an excel wizard, but have done some programming in it in the past.

I'm 99% sure you do need the "unload me". Your unload in the cancel is commented out with the '.

The method I've always used for referencing cells is quite a bit different. Can't see well enough right now to look it up for you, but I believe its something like sheet.cells(a,b) where a and b are the row and column (column "A" = 1 or maybe 0.)

Peter Stahl
01-13-2010, 3:13 PM
Doug & Matt, thanks for the reply. I put the unload me in and it worked.