On 29 Mar, 14:47, "John Coleman" <jcole...@franciscan.edu> 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, Pasty_The_Fi...@hotmail.com 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).LineStyle =
> > xlContinuous
> > Range("D13:E19").Borders(xlInsideVertical).LineStyle =
> > 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).LineStyle =
> > xlContinuous
> > Range("D13:E18").Borders(xlInsideVertical).LineStyle =
> > 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).LineStyle =
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).LineStyle =
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).LineStyle =
xlContinuous
Range("D10:E11").Borders(xlInsideVertical).LineStyle =
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).LineStyle =
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).LineStyle =
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).LineStyle =
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).LineStyle =
xlContinuous
Range("D13:E16").Borders(xlInsideVertical).LineStyle =
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).LineStyle =
xlContinuous
Range("D13:E19").Borders(xlInsideVertical).LineStyle =
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).LineStyle =
xlContinuous
Range("D13:E18").Borders(xlInsideVertical).LineStyle =
xlContinuous
End If
Call addNewSheet
Exit Sub
NoDepartment: MsgBox "You have not entered your department",
vbExclamation
Exit Sub
End Sub
Thanks for your help
|
Similar Threads
