Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying sheet or saving workbook kicks off ComboBox change Sub
I'm writing what (to me, anyway) is a relatively complicated spreadsheet highly dependent on VBA, and I'm running into a problem. I'm essentially creating a form for people to fill out, and have a combination of text boxes, combo boxes, and command buttons. At one point I need to have the user click on a button and create (copy) another sheet to fill out. The sheet is copied from a template. The copying part works fine. However, when the copy routine runs, it also executes other change subroutines from other combo boxes that should not be accessed at this point. Here's the relevant parts of my code: ===================================== Private Sub CoRole1_Change() MsgBox ("CoRole1") End Sub Private Sub CreateBio1_Click() Call AddBioSheet(CoLastName1, CoAlpha1) End Sub Private Sub EndMonth_Change() MsgBox ("EndMonth") End Sub Private Sub AddBioSheet(Lastname, Email) Dim WS As Worksheet For Each WS In Worksheets If WS.Name = Lastname Then MsgBox ("Biosheet exists") Exit Sub End If Next WS 'Problem occurs as this line of code is executed ActiveWorkbook.Sheets("BioData").Copy befo=ActiveWorkbook.Sheets("Year 1") Set WS = Sheets("BioData (2)") Sheets("BioData (2)").Name = Lastname WS.Range("A1").Value = Lastname WS.Range("A2").Value = Email Worksheets("CoverSheet").Activate End Sub =============================== The result of my triggering AddBioSheet is that if the user has selected data in any other combo box, the change code for that box is triggered as well. For example, if I've filled out the combo box associated with "CoRole1", when I trigger AddBioSheet, In this example I get *two* messages boxes that say "CoRole1". If I've selected the EndMonth, I get *two* msgboxes that say "EndMonth" and *two* messages boxes that say "CoRole1". I would like to run other code for the EndMonth and CoRole1 changes, but I can't have it executing every time someone copies a sheet. (FYI, in this troubleshooting example, the message boxes also appear when you do a "save as" of the workbook) I troubleshot the code down to the line that does the actual copy of the worksheet. It's as this point the messages come. If anyone can explain why this is happening, how to fix it, or a work-around, it would be greatly appreciated. Thank you. -shane dunlap |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data to new workbook and saving as text delimited | Excel Discussion (Misc queries) | |||
Copying data from workbook/sheets to another workbook/sheet | Excel Programming | |||
Saving a sheet in a workbook as .csv but not changing workbook name | Excel Programming | |||
combobox/copying sheet | Excel Discussion (Misc queries) | |||
Auto Populate sheet name of active workbook in Combobox in Command | Excel Programming |