Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 8th 11, 05:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 3
Default Stop Macro running when I save the workbook

Hi,

Please can you help, everytime I select save as on my excel
spreadsheet a macro runs which deletes all the values in the cell. I
need to keep the marco but I do not want it to run when I select save
as:

The macro should only run when a drop down list is changed which
populates data in the spreadsheet. The macro is not in a module but
resides in the workbook. How can I stop this macro running when I
select save as?

Private Sub Cash_Select_Change()

myYear = Year(Date)
LeapYear = "No"

If myYear = "2012" Or myYear = "2016" Or myYear = "2020" Or myYear =
"2024" Or myYear = "2028" Or myYear = "2032" Then
LeapYear = "Yes"
Else
LeapYear = "No"
End If


'Range("CURRENCY_1_FORECAST").ClearContents
'Range("CURRENCY_2_FORECAST").ClearContents
'Range("CURRENCY_3_FORECAST").ClearContents

If Cash_Forecast_Select.Value = "January" Then
Sheets("Cash Forecasting").Select
Range("January").Copy
Range("B17").Select
ActiveSheet.Paste
Range("B26").Select
ActiveSheet.Paste
Range("B35").Select
ActiveSheet.Paste
'Range("B44").Select
'ActiveSheet.Paste
'Range("B53").Select
'ActiveSheet.Paste
'Range("B62").Select
'ActiveSheet.Paste
Range("C19").Select
End If

End Sub

Thanks for any advice.

Brett

  #2   Report Post  
Old July 9th 11, 12:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1,522
Default Stop Macro running when I save the workbook

On Jul 8, 11:28*am, TyreDude wrote:
Hi,

Please can you help, everytime I select save as on my excel
spreadsheet a macro runs which deletes all the values in the cell. *I
need to keep the marco but I do not want it to run when I select save
as:

The macro should only run when a drop down list is changed which
populates data in the spreadsheet. *The macro is not in a module but
resides in the workbook. *How can I stop this macro running when I
select save as?

Private Sub Cash_Select_Change()

myYear = Year(Date)
LeapYear = "No"

If myYear = "2012" Or myYear = "2016" Or myYear = "2020" Or myYear =
"2024" Or myYear = "2028" Or myYear = "2032" Then
LeapYear = "Yes"
Else
LeapYear = "No"
End If

'Range("CURRENCY_1_FORECAST").ClearContents
'Range("CURRENCY_2_FORECAST").ClearContents
'Range("CURRENCY_3_FORECAST").ClearContents

If Cash_Forecast_Select.Value = "January" Then
Sheets("Cash Forecasting").Select
Range("January").Copy
Range("B17").Select
ActiveSheet.Paste
Range("B26").Select
ActiveSheet.Paste
Range("B35").Select
ActiveSheet.Paste
'Range("B44").Select
'ActiveSheet.Paste
'Range("B53").Select
'ActiveSheet.Paste
'Range("B62").Select
'ActiveSheet.Paste
Range("C19").Select
End If

End Sub

Thanks for any advice.

Brett


You should write your macros to avoid selections. I think you want
this in a worksheet_change event in the SHEET module tied to the drop
down cell. I see other errors.

"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I save the formulas when running a macro? Need ASAP!! MLP Excel Discussion (Misc queries) 2 November 14th 06 07:50 PM
Save To Csv Running A Macro stevekirk Excel Discussion (Misc queries) 2 August 11th 06 02:42 PM
How do I stop a Macro from running? JulianB Excel Worksheet Functions 1 April 10th 06 10:33 PM
How to CANCEL file SAVE PROMPT when MACRO is running? Stuart Macro Muppet Excel Discussion (Misc queries) 3 August 11th 05 12:26 PM
How to stop getting the file save box when running a macro Pank Mehta Excel Discussion (Misc queries) 1 March 29th 05 04:05 PM


All times are GMT +1. The time now is 11:38 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017