user-defined Cancel button in excel form

[cramz]

I created a userform which has several text boxes and 3 buttons.
The button I'm concerned with is the Cancel button, which I would like to "Cancel" the operation. Specifically, if any data was entered, I do not want it written to the worksheet.

What I would like to accomplish is to identify that the Cancel button (specifically) has been clicked; or how to determine/define which button, has been clicked.

The With statement looks like this

With frmEstimateRecord
    .lblEstimateNum.Caption = "Estimate Number: " & newEstimate.number
    .txtDate.Value = newEstimate.date
    .Show
    newEstimate.date = .txtDate.Value
    newEstimate.customer = .txtCustomer.Value
    newEstimate.jobname = .txtJob.Value
    newEstimate.description = .txtDescription.Value
End With

 

[Zathras]

It sounds like you are trying to do this all in a code module. The form has its own code page associated with it and you should put the bulk of your code there. Each button has a separate click event for which you can write code that pertains to the click of that button.

 

[SkipVought]

The Cancel Button has a Click Event.

In the design mode, double-click the cancel button. That's where your code belongs.

Skip,

_[red][/red]

 

[cramz]

Your suggestions are much appreciated, I'll give that a whirl.

 

[cramz]

Okay, I know where to find the different events and access the code portion of the userform. Check!

What you're saying is that I shouldn't do the processing in the worksheet module, but in the userform code section.

My question then becomes, can I pass a user-defined struct to the form?

If so, then I could write a routine that populates the form accordingly.

Then how do I get the data back?

From a bird's-eye view, what is being done is I am generating some data, an ID and a date. I then want the form to pop-up with that data in the form. Once the form is populated, the user can choose to save it to the workshee, perform a different action (yet to be written), or Cancel, and the data *poof* dissapears, never to be heard from again, and the form exits.

Suggestions?

 

[SkipVought]

I don't understand the dilema.

The user clicks Cancel and POOF! There's nothing left to put anywhere that is not already on the sheet.

Skip,

_[red][/red]

 

[cramz]

You are right.
Let me step back a sec.
In the code above, newEstimate is a data-type/struct I defined. It's nothing fancy at all, just a few strings. To this point, an ID has been generated and date has been stored in newEstimate. The purpose of the form is to get the rest of the data for use down later on.
Is there a way to pass newEstimate, in it's entirety, to the form? How so?

 

[cramz]

To go one step further, when another button on the form is clicked to save the data, how do I get that information back to the instantiating method?

 

[SkipVought]

So if you CANCEL, you DO or DO NOT want to pass the data on to the sheet?

If you DO, just execute the pass data routine before you POOF!

Skip,

_[red][/red]

 

[cramz]

If I Cancel, I DO NOT want the data on the sheet.
So I now understand that I should just NOT write the data to the sheet. Simple enough.
For the cases that I do want the data, is it possible to put the data from the form into the struct and then pass it back? Or do I continue using the method I originally listed? (which is what screwed my thinking up in the first place)

I just want to be able to pass the form my little structure and should I so choose, have it be passed back. The Cancel, Save, and other buttons are really just the minutia.

 

[SkipVought]

From a form you can write data on a sheet and read data from a sheet.

What's the question?

Skip,

_[red][/red]

 

[cramz]

This all stems from a double-click event in the worksheet.
For a certain case, the creation of a record begins: data is GENERATED, --AND-- this userform pops up to get the rest. The form has 3 buttons: Cancel, Save/Exit, and Save and do more stuff.
It's the Save and do more stuff, that requires the data which was entered by the user into the form. I want that data back in the instantiating method (Worksheet_BeforeDoubleClick) so that I can continue processing with that data.

The cancel(POOF) scenario is all taken care of.
I

My question is: How do I call the form, pass the data from Worksheet_BeforeDoubleClick, then have it pass back to Worksheet_BeforeDoubleClick?

 

[SkipVought]

Sub Worksheet_BeforeDoubleClick()
Routine ToPassDataToForm
Form.Show
'if don't go no futher cuz yer ALREADY on the form! _
So if you have more stuff to do here, make it a new sub _
and call it from the Form
End sub

Skip,

_[red][/red]

 

[cramz]

Here it comes...Ohhhh!
What you're saying is, don't worry about coming back to the instantiating method. Just go forward.

I was trying to come back to the instantiating method. I guess there's no reason why it has to be that way.

Okay.
Thanks Skip.

 

[SkipVought]

BTW, you're not really referring to a METHOD, but an EVENT.

Skip,

_[red][/red]

 

[cramz]

Thanks for the correction on the nomenclature. And again, thanks for the help, AND the patience.
-C