Macro runs unexpectedly

[MrFixItDave]

I created a couple of Excel macros using a ComboBox Click Event. When I go to save the file using File, SaveAs the Macro unexpectedly runs before the file is saved. The ComboBox name is AddPlant and event name is AddPlant_Click(). I cannot figure out why this is getting triggered when I go to save the file under a new file name. It does not trigger when I use the File, Save command. Any help is appreciated.

 

[GTO]

Greetings,

I think you'll need to post the code userform (or just the combo box if its on a sheet), as well as any code under the ThisWorkbook module.

Mark

 

[MrFixItDave]

Here is the Code. It is in the VBA project, Sheet1 (Plants) module. There is no other code in ThisWorkbook module.

Private Sub CBox_Plants_Click()
'Sets up the row for lookup of Plant Data
ActiveCell.Offset(0, 1).Range("A1").Select
PltDescCell = ActiveCell.Address
' Enters the Description (copies a Vlookup function)
Application.Goto Reference:="PltDescFunction"
Selection.Copy
Application.Goto Reference:=Worksheets("Plants").Range(PltDescCell), Scroll:=False
ActiveSheet.Paste
' Enters the Unit Price (copies a Vlookup function)
Application.Goto Reference:="PltPriceFunction"
Selection.Copy
Application.Goto Reference:=Worksheets("Plants").Range(PltDescCell), Scroll:=False
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
' Enters the Extended Price (Copies a formula)
Application.Goto Reference:=Worksheets("Plants").Range("ExtPriceFormula"), Scroll:=False
Selection.Copy
Application.Goto Reference:=Worksheets("Plants").Range(PltDescCell), Scroll:=False
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste
Worksheets("Plants").CBox_Plants.Visible = False
' Moves the screen to the beginning and selects the quantity cell
Range("PlantHomeCell").Select
Application.Goto Reference:=Worksheets("Plants").Range(PltDescCell), Scroll:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Application.ScreenUpdating = True
End Sub

I created a new file and copied the code to it. If this code has not been run, it will save correctly. Once this code has been run, it causes the code to run again on File SaveAs.

Thanks for your help!

 

[GTO]

Hi Dave,

I am afraid I am at a loss. You state that there is no code in ThisWorkbook module, so that pre-empts anything from BeforeSave etc.

Unless you have 'CBox_Plants_Click' being called from somewhere else in the sheet's module (and even that doesn't make sense as to the SaveAs firing), I cannot imagine what would cause this.

Hopefully someone spots something that I have not

Mark

 

[MrFixItDave]

Hi Mark,

Baffling isn't it? There is no code that calls the 'CBox_Plants_Click' event. It is called only when the Combo Box click event is triggered, or unwantingly, when the enter key is pressed for the 'File SaveAs" command to take place. Thanks for looking. I appreciate your help!
~Dave

 

[GTO]

Hi Mark,

...when the enter key is pressed for the 'File SaveAs" command to take place. Thanks for looking. I appreciate your help!
~Dave

Just to clarify, this is with the app's saveas diolog box displayed, correct?

 

[Ivan F Moala]

I created a couple of Excel macros using a ComboBox Click Event. When I go to save the file using File, SaveAs the Macro unexpectedly runs before the file is saved. The ComboBox name is AddPlant and event name is AddPlant_Click(). I cannot figure out why this is getting triggered when I go to save the file under a new file name. It does not trigger when I use the File, Save command. Any help is appreciated.

Hi, is that the ONLY code you have in the workbook.
You mention it is the AddPlant_Click event firing BUT you gave us CBox_Plants_Click
code ??

 

[MrFixItDave]

Ivan,

This is the correct code. I changes the name at one point hoping it would help, but it made no difference. The only other code in this module is triggered when a control button is pressed:

Private Sub AddPlantBtn_Click()
Application.ScreenUpdating = False
' Finds the next available row for data entry
Range("PlantHome").Select
If (ActiveCell.Offset(1, 0).Text = "") Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Worksheets("Plants").CBox_Plants.LinkedCell = ActiveCell.Address
'Opens the Plant Combo Box
Worksheets("Plants").CBox_Plants.Visible = True
End Sub


I also have another module of similar code "Sheet2(Services)":

Sub CBox_Other_Click()
'Sets up the row for lookup of Plant Data
TargetCell = ActiveCell.Address
' Checks to see if 'Installation of Plant Material' is selected
If CBox_Other.Text = "Installation of Plant Material" Then
' Enters the Extended Price based on .65% of plant cost
Application.Goto Reference:="InstallFunction"
Selection.Copy
Application.Goto Reference:=Worksheets("Services").Range(TargetCell), Scroll:=False
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste
Else
' Enters the Unit Price
Application.Goto Reference:="OtherPriceFunction"
Selection.Copy
Application.Goto Reference:=Worksheets("Services").Range(TargetCell), Scroll:=False
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
' Enters the Extended Price formula
Application.Goto Reference:="ServPriceFormula"
Selection.Copy
Application.Goto Reference:=Worksheets("Services").Range(TargetCell), Scroll:=False
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveSheet.Paste
End If
' Enter 'Labor' as the SKU
Application.Goto Reference:=Worksheets("Services").Range(TargetCell), Scroll:=False
ActiveCell.Offset(0, -1).Range("A1").Select
'ActiveCell.Text = "LABOR"
' Format the Price Cells
Columns("E:F").Select
Selection.NumberFormat = "$0.00"
Application.ScreenUpdating = True
' Moves the screen to the beginning and selects the quantity cell
Range("OtherHomeCell").Select
Application.Goto Reference:=Worksheets("Services").Range(TargetCell), Scroll:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Worksheets("Services").CBox_Other.Visible = False
End Sub

I tried modifying the code to "clean it up" and eliminate the need for the macro to copy certain cells and, instead, write the function or formula into the cell, but the problem still exists?????

I'm baffled!

Thanks, ~Dave

 

[MrFixItDave]

I forgot to answer an earlier question... Yes , this is with the app's saveas diolog box displayed? I am doing this to save the entire file. It is not Macro generated.<!-- / message -->