Calling my macro is only working if certain options are selected

P

Pasty_The_First

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
 
M

Mike

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
 
J

John Coleman

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
 
P

Pasty_The_First

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







- Show quoted text -
Click to expand...

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
 
J

John Coleman

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?
 
P

Pasty_The_First

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?
Click to expand...


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).LineStyle =
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Thin Borders 3

Top