Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cancel problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cancel problem
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 -- Rick (MVP - Excel) "DDawson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cancel problem
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cancel problem
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform cancel problem
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cancel Out of UserForm Return Focus | Excel Programming | |||
Userform Cancel | Excel Programming | |||
userform cancel button | Excel Programming | |||
Cancel a Procedure through a Userform | Excel Programming | |||
Cancel Button on Userform | Excel Programming |