Data Form Sort

Birdy92 · May 8th, 2002, 06:34 AM

I was trying to have a macro run (that is already made) once the user closes out the data form. I was told that using the following code would get this marco to run when I closed out the form but the only time that I can get it to run is when I close out the sheet. Am I doing something wrong here or is there more to the story??

Private Sub workbook_BeforeClose(Cancel As Boolean)
Move 'this is my macro's name
End Sub

The code is in the "ThisWorkbook" object and the data form opens automatically when Excel opens.

Rocky E · May 8th, 2002, 07:03 AM

Hi Birdy,

Well as usual I am more than a little confused? Each object has it's own methods. So a Wookbook fires on the workbook events. By data form are you refering to a Worksheet or a User Form or am I totaly of base here?

Rocky...

Birdy92 · May 8th, 2002, 07:15 AM

I understand your confusion, because I am just as confused as you on this...The main reason that I used that code was I got the same answer from two different source...When I start Excel I use the following:

Private Sub Workbook_Open()
Sheets("Test").Select
Range("A2").Select
ActiveSheet.ShowDataForm
End Sub

What I am trying to accomplish is when they click on the close button on the dataform is for the "Move" macro to run.

Hope this helps clear up both of our million points of confusion.

Rocky E · May 8th, 2002, 07:20 AM

Try
Private Sub UserForm_Deactivate()

End Sub

Private Sub UserForm_Terminate()

End Sub
I don't see how it can work in a Workbook method.

Still lost I guess.

Rocky...

[ This Message was edited by: Rocky E on 2002-05-08 07:23 ]

Birdy92 · May 8th, 2002, 07:22 AM

Rocky.....I hope that you missed posted here otherwise I am really confused now

Birdy92 · May 8th, 2002, 07:30 AM

Nevermind Rocky.....The link that got sent to me showed me something different than what you posted.....I will give this a shot.

Rocky E · May 8th, 2002, 07:37 AM

Sorry about that.

Well that is a start, but what does your datform look like? I still can't see a workbook method doing what you are asking. I just don't understand enough to recommend someting. Maybe someone else does?

Rocky...

Birdy92 · May 8th, 2002, 07:48 AM

The dataform is huge(about 24 items)....It is for our salemen to enter info for their jobs that they are working on...Right now they each have their own sheet in the workbook and all I am trying to do is to simplify the entering of their data.

I agree that the Workbook object may not be the best way to go, but this was the one answer I got to a previous post about two week ago on this board.

All I am trying to do is really clean up the entering of data for the people using this sheet. If you even have another idea on how to put this info in, and maybe the simpliest is to just put it in the old fasion way, let me know.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 06:34 AM	#1
Birdy92 New Member Join Date: Apr 2002 Posts: 14	I was trying to have a macro run (that is already made) once the user closes out the data form. I was told that using the following code would get this marco to run when I closed out the form but the only time that I can get it to run is when I close out the sheet. Am I doing something wrong here or is there more to the story?? Private Sub workbook_BeforeClose(Cancel As Boolean) Move 'this is my macro's name End Sub The code is in the "ThisWorkbook" object and the data form opens automatically when Excel opens.

May 8th, 2002, 07:03 AM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Hi Birdy, Well as usual I am more than a little confused? Each object has it's own methods. So a Wookbook fires on the workbook events. By data form are you refering to a Worksheet or a User Form or am I totaly of base here? Rocky...

May 8th, 2002, 07:15 AM	#3
Birdy92 New Member Join Date: Apr 2002 Posts: 14	I understand your confusion, because I am just as confused as you on this...The main reason that I used that code was I got the same answer from two different source...When I start Excel I use the following: Private Sub Workbook_Open() Sheets("Test").Select Range("A2").Select ActiveSheet.ShowDataForm End Sub What I am trying to accomplish is when they click on the close button on the dataform is for the "Move" macro to run. Hope this helps clear up both of our million points of confusion.

May 8th, 2002, 07:20 AM	#4
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Try Private Sub UserForm_Deactivate() End Sub Private Sub UserForm_Terminate() End Sub I don't see how it can work in a Workbook method. Still lost I guess. Rocky... [ This Message was edited by: Rocky E on 2002-05-08 07:23 ]

May 8th, 2002, 07:22 AM	#5
Birdy92 New Member Join Date: Apr 2002 Posts: 14	Rocky.....I hope that you missed posted here otherwise I am really confused now

May 8th, 2002, 07:30 AM	#6
Birdy92 New Member Join Date: Apr 2002 Posts: 14	Nevermind Rocky.....The link that got sent to me showed me something different than what you posted.....I will give this a shot.

May 8th, 2002, 07:37 AM	#7
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Sorry about that. Well that is a start, but what does your datform look like? I still can't see a workbook method doing what you are asking. I just don't understand enough to recommend someting. Maybe someone else does? Rocky...

May 8th, 2002, 07:48 AM	#8
Birdy92 New Member Join Date: Apr 2002 Posts: 14	The dataform is huge(about 24 items)....It is for our salemen to enter info for their jobs that they are working on...Right now they each have their own sheet in the workbook and all I am trying to do is to simplify the entering of their data. I agree that the Workbook object may not be the best way to go, but this was the one answer I got to a previous post about two week ago on this board. All I am trying to do is really clean up the entering of data for the people using this sheet. If you even have another idea on how to put this info in, and maybe the simpliest is to just put it in the old fasion way, let me know.