Userform cancel problem

[DDawson]

I have a button on the workbook that calls up a macro that in turn displays
an inputbox type userform. I can't get the cancel button to cancel out of the
macro, i.e. the macro still runs.

How do I stop the macro from running when I click cancel?

Dylan

 

[Rick Rothstein]

Is it a UserForm that looks like an InputBox or is it an actual InputBox
called by the InputBox command? If the former, you will have to show us your
the code you have behind the Close button; if the latter, then just test for
the return from the InputBox command to have text in it. Something like this
maybe...

ReturnValue = InputBox("Your prompt message")
If Len(ReturnValue) > 0 Then
'
' The user entered something so
' process it with your code here
'
Else
' This section is optional; use it only if you need to
' do something when the user enters nothing
End If

 

[Tom Hutchins]

If the length of the return value from the inputbox is zero, nothing was
entered or Cancel was clicked:

Sub AAAAAA()
Dim retval
retval = InputBox("Enter something", "My title", "XYZ")
If Len(retval) = 0 Then Exit Sub
MsgBox "Didn't cancel; entered something"
End Sub

Hope this helps,

Hutch

 

[DDawson]

Hi Rick

Its a userform "frmEnterName" that looks like an inputbox. CommandButton2 is
the cancel button. I got it to work, but now I have another problem.

Previously I used the button on the worksheet to call the InsertData macro,
which then called the frmEnterName to get the value, but I couldn't get the
form to cancel.

It now works, when I call frmEnterName from the worksheet button and the
macro from the Buttonclick event. BUT, it isn't capturing the strEnterName
that is the string needed for the InsertData macro.

The underlying macro takes the entered value, then imports data filtered by
that value.

How do I pass the data from the textbox into my InsertData macro?

Private Sub btnTitle_Click()
strEnterName = txtTitle.Value
Call InsertData
End Sub

Private Sub CommandButton2_Click()
'strEnterName = ""
Unload Me
frmEnterName.Hide
End Sub

Sub InsertData()

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
frmEnterName.txtTitle.Value = strEnterName
frmEnterName.Hide
'IF frmEnterName.CommandButton2

Windows("Export_Requests.csv").Activate
'Sheets("Export_Requests").Select
'ActiveSheet.ShowAllData
Range("A1").Select
'ActiveWorkbook.Sheets(1).ActiveSheet.ShowAllData
Selection.AutoFilter
Selection.AutoFilter Field:=12, Criteria1:="*Power*"
Selection.AutoFilter Field:=14, Criteria1:=strEnterName
strEnterName = ""
etc...

 

[Chip Pearson]

If I need to determine whether a user cancelled input from a userform
(either with a Cancel command button or the "X" on the form's caption
bar), I create a public read-only property called UserCancelled and
test that in the code that launched the form. E.g., in your form's
code module:

Private bUserCancelled As Boolean

Public Property Get UserCancelled() As Boolean
UserCancelled = bUserCancelled
End Property

Private Sub btnCancel_Click()
bUserCancelled = True
Me.Hide
End Sub

Private Sub btnOK_Click()
bUserCancelled = False
Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
bUserCancelled = True
Me.Hide
End Sub


Then, in a regular code module, use code like

Sub AAA()
UserForm1.Show
If UserForm1.UserCancelled = True Then
Debug.Print "Cancel"
Else
Debug.Print "OK"
End If
Unload UserForm1
End Sub

In this code, you don't Unload the form from within the form's code
module. Instead, just Hide the form. When you Unload the form, it is
dumped from memory and you can't get the values of the controls on the
form. When you Hide a form, you simply make it invisible but it
remains loaded in memory and its properties and controls remain
accessible.

Private Sub CommandButton2_Click()
'strEnterName = ""
Unload Me
frmEnterName.Hide
End Sub

Get rid of the Unload Me line of code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)