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)




On Wed, 15 Oct 2008 10:42:01 -0700, DDawson
wrote:

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