| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling my macro is only working if certain options are selected
Hi there I have attached at the bottom a piece of code from a
questionnaire I have made (with lots of help) and it calls a macro called addNewSheet at the end - for some reason this only works if they select yes for the final bit and if they select no nothing happens. Would anyone know where I am going wrong? detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
Which ever way this piece of macro executes it will always end up calling the
addnewsheet macro. I notice we only have questions 4E and 4F here so on the assumption there are earlier questions then it may be that something in an earlier part of the macro is causing your problem. Mike " wrote: Hi there I have attached at the bottom a piece of code from a questionnaire I have made (with lots of help) and it calls a macro called addNewSheet at the end - for some reason this only works if they select yes for the final bit and if they select no nothing happens. Would anyone know where I am going wrong? detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
perhaps there is something within the addnewsheet macro?
" wrote: Hi there I have attached at the bottom a piece of code from a questionnaire I have made (with lots of help) and it calls a macro called addNewSheet at the end - for some reason this only works if they select yes for the final bit and if they select no nothing happens. Would anyone know where I am going wrong? detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
Hi
Mike is correct that sub addNewSheet will be called no matter what - so it would help if you included the code for the sub and a description of what you expect it to do. One thought - you described this code fragment as being on the bottom of a larger sub. If you have an On Error Resume Next statement earlier (a dangerous statement in general) and addNewSheet hits an error condition in the case "no" was selected, then it might appear as if nothing happened whereas in reality a bug was encountered then swept under the rug. If so, comment out the On Error statement and see what happens. Did you try stepping through the macro to see what happens? Hth -John Coleman On Mar 29, 8:51 am, wrote: Hi there I have attached at the bottom a piece of code from a questionnaire I have made (with lots of help) and it calls a macro called addNewSheet at the end - for some reason this only works if they select yes for the final bit and if they select no nothing happens. Would anyone know where I am going wrong? detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
On 29 Mar, 14:47, "John Coleman" wrote:
Hi Mike is correct that sub addNewSheet will be called no matter what - so it would help if you included the code for the sub and a description of what you expect it to do. One thought - you described this code fragment as being on the bottom of a larger sub. If you have an On Error Resume Next statement earlier (a dangerous statement in general) and addNewSheet hits an error condition in the case "no" was selected, then it might appear as if nothing happened whereas in reality a bug was encountered then swept under the rug. If so, comment out the On Error statement and see what happens. Did you try stepping through the macro to see what happens? Hth -John Coleman On Mar 29, 8:51 am, wrote: Hi there I have attached at the bottom a piece of code from a questionnaire I have made (with lots of help) and it calls a macro called addNewSheet at the end - for some reason this only works if they select yes for the final bit and if they select no nothing happens. Would anyone know where I am going wrong? detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet End Sub- Hide quoted text - - Show quoted text - Hi there here is the code for addNewSheet (the resetSheet just clears the information) Sub addNewSheet() If Range("A2").Value = "" Then GoTo theend: ActiveSheet.Name = Range("a2").Value ActiveSheet.Copy After:=ActiveSheet ActiveSheet.Name = "Sheet1" Call resetSheet ActiveWindow.View = xlPageBreakPreview ActiveWindow.Zoom = 100 ActiveSheet.Previous.Select ActiveSheet.Visible = xlVeryHidden ActiveWorkbook.Save Exit Sub theend: MsgBox "You have not stated your department" End Sub Here is the other code for rest of the questionnaire Sub Questionaire() Dim Msg1, Msg2, Msg3, Msg4, Msg5 Msg1 = "Question 1. Do you store any COMMERCIAL information offline (i.e. stored on your C-drive in your 'My Documents'folder or in drives that you have chosen to be able to view offline)?" Msg2 = "Question 3. Do you have any commercial need to store information on your C-drive?" Msg3 = "Question 4. Have you ever known of an incident in your area where a portable device has been lost or stolen?" Msg4 = "Question 4c. Could this information have been detrimental to Business Operations or Customers if it fell into the wrong hands?" Msg5 = "Question 2. Do you store any CUSTOMER information offline (i.e. stored on your C-drive in your 'My Documents'folder or in drives that you have chosen to be able to view offline)?" Application.ScreenUpdating = True If Range("A2") = "" Then GoTo NoDepartment storeSI = MsgBox(Msg1, vbYesNo + vbQuestion) If storeSI = vbYes Then Range("D2") = Msg1 Range("E2") = "Yes" Columns("E").AutoFit Range("D2:E4").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D2:E4").Borders(xlInsideHorizontal).LineSty le = xlContinuous Range("D2:E4").Borders(xlInsideVertical).LineStyle = xlContinuous sortSI = InputBox("Question 1b. What sort of information is held?") Range("D3") = "Question 1b. What sort of information is held?" Range("E3") = sortSI Range("E3").Font.ColorIndex = 5 frmRadio1.Show If frmRadio1.rdPDA = True Then Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = "PDA" Range("E4").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio1.Hide Else Range("D4") = "Question 1c. Please state whether the information is held on a PDA or laptop" Range("E4") = "Laptop" Range("E4").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio1.Hide End If Else Range("D2") = Msg1 Range("E2") = "No" Columns("E").AutoFit Range("D2:E2").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D2:E2").Borders(xlInsideVertical).LineStyle = xlContinuous End If myDocs = MsgBox(Msg5, vbYesNo + vbQuestion) If myDocs = vbYes Then Range("D6") = Msg5 Range("E6") = "Yes" Columns("E").AutoFit Range("D6:E8").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D6:E8").Borders(xlInsideHorizontal).LineSty le = xlContinuous Range("D6:E8").Borders(xlInsideVertical).LineStyle = xlContinuous sortSI = InputBox("Question 1b. What sort of information is held?") Range("D7") = "Question 2b. What sort of information is held?" Range("E7") = sortSI Range("E7").Font.ColorIndex = 5 frmRadio1.Show If frmRadio1.rdLaptop = True Then Range("D8") = "Question 2c. Please state whether the information is held on a PDA or laptop" Range("E8") = "Laptop" Range("E8").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio1.Hide Else Range("D8") = "Question 2c. Please state whether the information is held on a PDA or laptop" Range("E8") = "PDA" Range("E8").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio1.Hide End If Else Range("D6") = Msg5 Range("E6") = "No" Columns("E").AutoFit Range("D6:E6").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D6:E6").Borders(xlInsideVertical).LineStyle = xlContinuous End If storeCdrv = MsgBox(Msg2, vbYesNo + vbQuestion) If storeCdrv = vbYes Then Range("D10") = Msg2 Range("E10") = "Yes" Columns("E").AutoFit Range("D10:E11").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D10:E11").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D10:E11").Borders(xlInsideVertical).LineSty le = xlContinuous HrdDrv = InputBox("Question 3a. Please state what the commercial need is") Range("D11") = "Question 3a. Please state what the commercial need is" Range("E11") = HrdDrv Range("E11").Font.ColorIndex = 5 Else Range("D10") = Msg2 Range("E10") = "No" Columns("E").AutoFit Range("D10:E10").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D10:E10").Borders(xlInsideVertical).LineSty le = xlContinuous End If portThft = MsgBox(Msg3, vbYesNo + vbQuestion) If portThft = vbYes Then Range("D13") = Msg3 Range("E13") = "Yes" Range("E13").Font.ColorIndex = 5 Else Range("D13") = Msg3 Range("E13") = "No" Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E13").Borders(xlInsideVertical).LineSty le = xlContinuous Exit Sub End If frmRadio2.Show If frmRadio2.rdPDA1 = True Then Range("D14") = "Question 4a. What was stolen a PDA(s), Laptop(s) or both?""" Range("E14") = "PDA" Range("E14").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio2.Hide ElseIf frmRadio2.rdLaptop1 = True Then Range("D14") = "Question 4a. What was stolen a PDA(s), Laptop(s) or both?""" Range("E14") = "Laptop" Range("E14").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio2.Hide Else Range("D14") = "Question 4a. What was stolen a PDA(s), Laptop(s) or both?""" Range("E14") = "Both" Range("E14").Font.ColorIndex = 5 Columns("E").AutoFit frmRadio2.Hide End If howMany = InputBox("Question 4b. How many such devices have been stolen that you were aware of?") Range("D13") = Msg3 Range("E13") = "Yes" Range("D14") = "Question 4a. What was stolen a PDA(s), Laptop(s) or both?" Range("D15") = "Question 4b. How many such devices have been stolen that you were aware of?" Range("E15") = howMany Range("E13").Font.ColorIndex = 5 Range("E14").Font.ColorIndex = 5 Range("E15").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E15").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E15").Borders(xlInsideHorizontal).LineS tyle = xlContinuous myDocs1 = MsgBox("Question 4c. For the incident/each of the incidents was there any information held offline at the time (i.e. held on you C-drive, in your 'My Documents' folder or in drives that you have chosen to be able to view offline)?", vbYesNo) If myDocs1 = vbYes Then whatStolen = InputBox("Question 4d. What information was held offline?") Range("D16") = "Question 4c. For the incident/each of the incidents was there any information held offline at the time (i.e. held on you C-drive, in your 'My Documents' folder or in drives that you have chosen to be able to view offline)?" Range("E16") = "Yes" Range("E16").Font.ColorIndex = 5 Range("D17") = "Question 4d. What information was held offline?" Range("E17") = whatStolen Range("E17").Font.ColorIndex = 5 Else Range("D16") = "Question 4c. For the incident/each of the incidents was there any information held offline at the time (i.e. held on you C-drive, in your 'My Documents' folder or in drives that you have chosen to be able to view offline)?" Range("E16") = "No" Range("D13:E16").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E16").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E16").Borders(xlInsideVertical).LineSty le = xlContinuous Exit Sub End If detri = MsgBox("Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?", vbYesNo) If detri = vbYes Then Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "Yes" Range("E18").Font.ColorIndex = 5 WhyDetri = InputBox("Question 4f. Why?") Range("D19") = "Question 4f. Why?" Range("E19") = WhyDetri Range("E19").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E19").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E19").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E19").Borders(xlInsideVertical).LineSty le = xlContinuous Else Range("D18") = "Question 4e. Could this information have had a detrimental effect on KM if it fell into the wrong hands?" Range("E18") = "No" Range("E18").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E18").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E18").Borders(xlInsideHorizontal).LineS tyle = xlContinuous Range("D13:E18").Borders(xlInsideVertical).LineSty le = xlContinuous End If Call addNewSheet Exit Sub NoDepartment: MsgBox "You have not entered your department", vbExclamation Exit Sub End Sub Thanks for your help |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
Hi
(code snipped) addNewSub() should work the same no matter what button you clicked on the message box - you make a copy of the current sheet, rename it, clear and relabel as sheet 1 the original then hide the copy. (no?) What do you mean when you say "it does nothing?" For debugging purposes maybe comment out the lines ActiveSheet.Visible = xlVeryHidden ActiveWorkbook.Save and run it. At the end of the run - just what (in the case you selected no) is wrong with the active sheet? Was a copy in fact made? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calling my macro is only working if certain options are selected
On 29 Mar, 17:36, "John Coleman" wrote:
Hi (code snipped) addNewSub() should work the same no matter what button you clicked on the message box - you make a copy of the current sheet, rename it, clear and relabel as sheet 1 the original then hide the copy. (no?) What do you mean when you say "it does nothing?" For debugging purposes maybe comment out the lines ActiveSheet.Visible = xlVeryHidden ActiveWorkbook.Save and run it. At the end of the run - just what (in the case you selected no) is wrong with the active sheet? Was a copy in fact made? Hi there I have worked it out - its because I put in an exit sub at the end of this bit Else Range("D13") = Msg3 Range("E13") = "No" Range("E13").Font.ColorIndex = 5 Columns("E").AutoFit Range("D13:E13").BorderAround ColorIndex:=xlAutomatic, Weight:=xlMedium Range("D13:E13").Borders(xlInsideVertical).LineSty le = xlContinuous Exit Sub I have put in the Call addNewSheet bit in just before the exit sub and this works now. Thanks a lot for you all your help. Regards Matt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Protect selected settings in 'options' | Excel Worksheet Functions | |||
| how do I display list options as different colour when selected? | Excel Worksheet Functions | |||
| Working with options from within Tools Options clears the Clipboar | Excel Programming | |||
| Calling in data from a selected cell | Excel Programming | |||
| Calling another workbook not working | Excel Worksheet Functions | |||
Linear Mode
