Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAveAs executes combobox code
Hi
We have a workbook that has a number of worksheets with comboboxes that have a list of values. When a selection is made, code is executed to change the appearance of the associated worksheet using the object_change event. Unfortunately when SaveAs is used, this event appears to be triggered and all the code is executed for all the worksheets with a combobox with a change event defined. This leaves the user faced with a different worksheet to the one they were observing prior to issuing the SaveAs command. Is there an easy way to stop this happening? Or is there an easy way to return them to the worksheet they were observing prior to issuing the SaveAs command? My testing has indicated that the _click event is also triggered by SaveAs, the AfterUpdate event is not triggered for SaveAs, but also not triggered by mouse selection from the list and the BeforeSave event is actioned prior to the _change event when using SaveAs. Thanks Alan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAveAs executes combobox code
Alan
What version of Excel are you using? Do you have anything in the BeforeSave event? Do your comboboxes have anything in the LinkedCell properties? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Alan Lyall" wrote in message . au... Hi We have a workbook that has a number of worksheets with comboboxes that have a list of values. When a selection is made, code is executed to change the appearance of the associated worksheet using the object_change event. Unfortunately when SaveAs is used, this event appears to be triggered and all the code is executed for all the worksheets with a combobox with a change event defined. This leaves the user faced with a different worksheet to the one they were observing prior to issuing the SaveAs command. Is there an easy way to stop this happening? Or is there an easy way to return them to the worksheet they were observing prior to issuing the SaveAs command? My testing has indicated that the _click event is also triggered by SaveAs, the AfterUpdate event is not triggered for SaveAs, but also not triggered by mouse selection from the list and the BeforeSave event is actioned prior to the _change event when using SaveAs. Thanks Alan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAveAs executes combobox code
break your linked cell links and possibly your listfillrange links. Manage
the populating of the list and writing of results with code and I don't think you will have this problem. -- Regards, Tom Ogilvy "Alan Lyall" wrote in message . au... Hi We have a workbook that has a number of worksheets with comboboxes that have a list of values. When a selection is made, code is executed to change the appearance of the associated worksheet using the object_change event. Unfortunately when SaveAs is used, this event appears to be triggered and all the code is executed for all the worksheets with a combobox with a change event defined. This leaves the user faced with a different worksheet to the one they were observing prior to issuing the SaveAs command. Is there an easy way to stop this happening? Or is there an easy way to return them to the worksheet they were observing prior to issuing the SaveAs command? My testing has indicated that the _click event is also triggered by SaveAs, the AfterUpdate event is not triggered for SaveAs, but also not triggered by mouse selection from the list and the BeforeSave event is actioned prior to the _change event when using SaveAs. Thanks Alan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SAveAs executes combobox code
Thanks Tom and Dick
We did not have any linked cells as such, but the code makes extensive use of listfillranges. This is probably causing the problem. However the workbook has too extensive use of these to rewrite another person's code easily. I have instead used the workbook_beforeSave to save the activesheet.name into a named range cell and then restored this from the final _change macro executed after the SaveAs. However this is dirty, and I seemed to have trouble referring to this named range after the save as, even though it worked in the beforesave event trap. I had to refer to the cell directly. It seems as if the named ranges are not recognised at this time. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox Code | New Users to Excel | |||
VBA line of code executes in Immediate Window but not in Code Window | Excel Discussion (Misc queries) | |||
ComboBox code | Excel Worksheet Functions | |||
URGENT - Need date format for SaveAs code | Excel Programming | |||
Count # of Times Sub Executes | Excel Programming |