struggling with vbYes and vbNo coding

[CharlieG]

Hi all,

another simple question on at the beginning of my struggles / learning with VBA! thank you for your patience for someone who struggles with printed book style instructions/learning but finds it easier with commented code!

code below worked fine until i tried to add in a pair of questions using MsgBox with vbYesNo!

originally i added in the If Ans = vbNo lines but clicking the no responses did not do what i needed... ie go to the next question for the first no and then going to the Main sheet for the second. got it to work by the code going through each question then switching to the main sheet at the end which should only happen if all questions are answered by a No.

would be hugely grateful if you could look at the code and make commented amendments / suggestions so that i can understand the mechanics of the code and learn through doing.


' DuplicateTemplateSheet Macro
' Keyboard Shortcut: Ctrl+Shift+D
'
Sheets("sheet template").Copy Before:=Sheets("sheet template")
Sheets("sheet template (2)").Select
Range("A2").Select
' Rename the new worksheet
NewName = InputBox("What is the sheet name?")
ActiveSheet.Name = NewName
' Question series - National or Bespoke or Mellish Document type
Msg = "Is the sheet for National/International standards?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
Sheets("national international").Select

'If Ans -vbNo then
'PLEASE HELP HERE - IF NO, THEN GO TO THE NEXT QUESTION

End If
Msg = "Is the sheet for customer documents?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
Sheets("customers").Select

'If Ans -vbNo then
'PLEASE HELP HERE - IF NO, THEN GO TO THE MAIN SHEET (THIS TO ONLY HAPPEN IF THE second answer is a no)

End If
' Return to the main menu
Sheets("main menu").Select
Range("A1").Select
'Reminder to add hyperlink buttons on relevant menu
MsgBox ("Make sure you add hyperlink buttons to the new sheet on the relevant Interface page")
End Sub


have put the main issues in capital letters to highlight them a little.

Thank you as always! (a few simple things are starting to make sense for me but i fear it will be a long slow process

[Bob Phillips]

Try this


' DuplicateTemplateSheet Macro
' Keyboard Shortcut: Ctrl+Shift+D
'
Worksheets("sheet template").Copy Before:=Sheets("sheet template")
Worksheets("sheet template (2)").Select
Range("A2").Select

' Rename the new worksheet
NewName = InputBox("What is the sheet name?")
ActiveSheet.Name = NewName

' Question series - National or Bespoke or Mellish Document type
If MsgBox("Is the sheet for National/International standards?", vbYesNo) = vbYes Then

Worksheets("national international").Select
ElseIf MsgBox("Is the sheet for customer documents?", vbYesNo) = vbYes Then

Worksheets("customers").Select
Else

Worksheets("main menu").Select
Range("A1").Select
End If

'Reminder to add hyperlink buttons on relevant menu
MsgBox "Make sure you add hyperlink buttons to the new sheet on the relevant Interface page"
End Sub

[CharlieG]

thank you again xld! seeing the code and ways of doing this, helps me far more than text books or videos online!