Navigate Via Drop Down List.

VinceF

VinceF

Board Regular
Joined
Sep 22, 2007
Messages
172
Office Version
  1. 2016
Platform
  1. Windows
Hello Experts,

Hopefully this is an easy one for the experts.
I created a drop down list and I would like to be able to navigate the sheets contained in that list which are in the workbook.
I suppose the cleanest way would be to you VBA...doing so is over my pay grade...help please.

VinceF
Excel 2016
Win10
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
I am assuming you are refering to a Data Validation list . If so, then you could use something like this:

In the sheet module:
VBA Code: 
Option Explicit

Const VALIDATION_CELL = "B1"  '<== change validation cell as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range(VALIDATION_CELL).Address Then
        Sheets(Target.Value).Select
    End If
End Sub
 
Upvote 0
Jaafar,
I very much appreciate your reply.
I have very limited VBA skills, allow me to provide further detail of what it is I'm hoping to accomplish and ask for further explanation on how to implement the code that you provided.
I have 11 sheets, 1 called Main where yes, what I have is a Data Validation List. In the list are the names of the remaining 10 sheets. My hope is to be able to navigate to those sheets by selecting them via the drop down list.

In VBA, I selected "Sheet 2" and opened up a code box (sorry for the lack of technical description) and inserted the code in the box. Closed the VBA and went to the drop down list and selected "Sheet 2", nothing happened. I'm sure that I'm supposed to do something else but lack the knowledge of what it is I'm supposed to do. It seems like I should be inserting the name of the sheet that I want it to go to or something...

Your continued assistance would be appreciated.

VinceF
 
Upvote 0
It appears that a bit more detailed info may be in order...

Mr. Tribak offered this code but as mentioned I'm unable to get it to work.

VBA Code:
Option Explicit

Const VALIDATION_CELL = "B1" '<== change validation cell as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range(VALIDATION_CELL).Address Then
Sheets(Target.Value).Select
End If
End Sub

I have 11 sheets, First sheet called "Main", in cell B2 of the Main sheet I have the Data Validation drop down list naming the remaining 10 sheets. Sheet 2 is called "Scramble", do I put this code in the "Main" sheet, or the "Scramble" sheet? Do I need to name the sheets in the code somewhere? Where he instructs to change validation code as needed...what goes in there?
Thanks in advance for your assistance

VinceF
Excel 2016
Win10
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Navigate Via Data Validation Drop Down List
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Navigate Via Data Validation Drop Down List
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Fluff,

My Bad, apologies...I wasn't aware of the rule.
I'm simply trying to get an answer to the question..
Usually the experts readily offer their assistance...for whatever reason this time it isn't happening.

Can you help?
 
Upvote 0
Thanks for the reply, yes I did change it to B2... I assume the code is supposed to go into the "main" page where the data validation is located?
 
Upvote 0
Here is a copy of the VBA that I have on the Main page where the drop down list is located in cell B2
Hope this helps



Private Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " WARNING !!!! WARNING !!! WARNING !!! This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

On Error Resume Next
Sheets("main").Range("b2").Value = "NAVIGATION"
Sheets("main").Range("d2").Value = "TEAM GEN"
Sheets("main").Range("E2").Value = "HISTORY"
Sheets("main").Range("F2").Value = "TWO PLUS"
Sheets("main").Range("G2").Value = "SKINS FEE"
Sheets("main").Range("H2").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J2").Value = "CTP'S FEE"
Sheets("main").Range("B4").Value = "CTP"
Sheets("main").Range("D4").Value = "GAME TYPE"
Sheets("main").Range("G4").Value = ""
Sheets("main").Range("H4").Value = ""
Sheets("main").Range("J4").Value = ""
Sheets("main").Range("b6").Value = "# 2"
Sheets("main").Range("b7").Value = "# 5"
Sheets("main").Range("b8").Value = "#11"
Sheets("main").Range("b9").Value = "#18"
Sheets("main").Range("c6:c9").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("D6").Value = "STROKE ADJUSTMENT"
Sheets("main").Range("D7").Value = ""
Sheets("main").Range("D8").Value = "SKINS FRONT"
Sheets("main").Range("D9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("F6").Value = "PAID OUT"
Sheets("main").Range("F7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("F8").Value = "SKINS BACK"
Sheets("main").Range("F9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("G6").Value = "# PLAYERS"
Sheets("main").Range("G7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("G8").Value = "TOTAL SKINS"
Sheets("main").Range("G9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("H6").Value = "SKIN POOL"
Sheets("main").Range("H7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("H8").Value = "SKIN VALUE"
Sheets("main").Range("H9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J6").Value = "CTP POOL"
Sheets("main").Range("l7:ag9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J8").Value = "CTP VALUE"
Sheets("main").Range("j9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("B11").Value = "CTP"
Sheets("main").Range("C11").Value = "ALT TEE"
Sheets("main").Range("D11").Value = "TEAM REQ"
Sheets("main").Range("E11").Value = "PLAYER"
Sheets("main").Range("F11").Value = "INDEX"
Sheets("main").Range("G11").Value = "HDCP"
Sheets("main").Range("H11").Value = "STROKES"
Sheets("main").Range("I11").Value = "QUOTA"
Sheets("main").Range("J11").Value = "ADJ QUOTA"
Sheets("main").Range("J11").Value = ""

Sheets("main").Range("c6:c9").ClearContents
Sheets("main").Range("aj7:an7").ClearContents


Sheets("main").Range("G12:J51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("b12:b51").Value = ""
Sheets("main").Range("c12:c51").Value = ""
Sheets("main").Range("d12:d51").Value = ""
Sheets("main").Range("e12:e51").Value = ""
Sheets("main").Range("f12:f51").Value = ""
Sheets("main").Range("L2").Value = "TEE"
Sheets("main").Range("N2").Value = "RATING"
Sheets("main").Range("P2").Value = "SLOPE"
Sheets("main").Range("R2").Value = "COR FAC"
Sheets("main").Range("T2").Value = "MAIN"
Sheets("main").Range("Z2").Value = "PLAYING CONDITIONS"
Sheets("main").Range("AF2").Value = "BUNKERS"
Sheets("main").Range("L4").Value = "SELECT TEE"
Sheets("main").Range("N4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("P4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("R4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("T4").Value = "SELECT COURSE"
Sheets("main").Range("Z4").Value = "PLAYING CONDITIONS"
Sheets("main").Range("AF4").Value = "BUNKER RELIEF"
Sheets("main").Range("L7:G9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("Z11").Value = "SKINS"
Sheets("main").Range("M11").Value = "FRONT"
Sheets("main").Range("V11").Value = "IN"
Sheets("main").Range("W11").Value = "BACK"
Sheets("main").Range("AF11").Value = "OUT"
Sheets("main").Range("AG11").Value = "TOTALS"
Sheets("main").Range("AH11").Value = "POINTS"
Sheets("main").Range("L12:U51").Value = ""
Sheets("main").Range("V12:V51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("W12:AE51").Value = ""
Sheets("main").Range("AF12:AH51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ4").Value = "POINT / PLACE"
Sheets("main").Range("AJ6").Value = "# PLACES PAID"
Sheets("main").Range("AK6").Value = "% FOR 1ST PLACE"
Sheets("main").Range("AL6").Value = "% FOR 2ND PLACE"
Sheets("main").Range("AM6").Value = "% FOR 3RD PLACE"
Sheets("main").Range("AJ7:AM7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ8").Value = "# OF PLAYERS"
Sheets("main").Range("AK8").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AL8").Value = "PLAYERS IN POSITIVE"
Sheets("main").Range("AM8").Value = "$ PER POINT"
Sheets("main").Range("AN8").Value = "PD OUT"
Sheets("main").Range("AJ9:AN9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ11").Value = "PTS vs QUOTA"
Sheets("main").Range("AK11").Value = "PLACE PAY OUT"
Sheets("main").Range("AL11").Value = "POINT PAY OUT"
Sheets("main").Range("AM11").Value = "SKINS PAY OUT"
Sheets("main").Range("AN11").Value = "# SKINS"
Sheets("main").Range("AJ12:AN51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("i12:i51").SpecialCells(xlConstants).ClearContents

Sheets("TEAM GENERATOR").Range("l2").Value = "TEAMS"
Sheets("TEAM GENERATOR").Range("I6").Value = "TEAM 1"
Sheets("TEAM GENERATOR").Range("M6").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O6").Value = "TEAM 2"
Sheets("TEAM GENERATOR").Range("S6").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I13").Value = "TEAM 3"
Sheets("TEAM GENERATOR").Range("M13").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O13").Value = "TEAM 4"
Sheets("TEAM GENERATOR").Range("S13").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I20").Value = "TEAM 5"
Sheets("TEAM GENERATOR").Range("M20").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O20").Value = "TEAM 6"
Sheets("TEAM GENERATOR").Range("S20").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I27").Value = "TEAM 7"
Sheets("TEAM GENERATOR").Range("M27").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O27").Value = "TEAM 8"
Sheets("TEAM GENERATOR").Range("S27").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I34").Value = "TEAM 9"
Sheets("TEAM GENERATOR").Range("M34").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O34").Value = "TEAM 10"
Sheets("TEAM GENERATOR").Range("S34").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("i7:i10").Value = ""
Sheets("TEAM GENERATOR").Range("i14:i17").Value = ""
Sheets("TEAM GENERATOR").Range("i21:i24").Value = ""
Sheets("TEAM GENERATOR").Range("i28:i31").Value = ""
Sheets("TEAM GENERATOR").Range("i35:i38").Value = ""
Sheets("TEAM GENERATOR").Range("o7:eek:10").Value = ""
Sheets("TEAM GENERATOR").Range("o14:eek:17").Value = ""
Sheets("TEAM GENERATOR").Range("o21:eek:24").Value = ""
Sheets("TEAM GENERATOR").Range("o28:eek:31").Value = ""
Sheets("TEAM GENERATOR").Range("o35:eek:38").Value = ""
Sheets("Team Generator").Range("m7:m39").SpecialCells(xlConstants).ClearContents
On Error Resume Next
Sheets("Team Generator").Range("s7:S39").SpecialCells(xlConstants).ClearContents
On Error GoTo 0

Sheets("scramble").Range("d6:d9").Value = ""
Sheets("scramble").Range("d14:d17").Value = ""
Sheets("scramble").Range("d22:d25").Value = ""
Sheets("scramble").Range("d30:d33").Value = ""
Sheets("scramble").Range("d38:d41").Value = ""
Sheets("scramble").Range("d46:d49").Value = ""
Sheets("scramble").Range("d54:d57").Value = ""
Sheets("scramble").Range("d62:d65").Value = ""
Sheets("scramble").Range("d70:d73").Value = ""
Sheets("scramble").Range("d78:d81").Value = ""
Sheets("scramble").Range("d86:d89").Value = ""
Sheets("scramble").Range("d94:d97").Value = ""

On Error Resume Next
Sheets("TWO PLUS").Range("AU4").Value = "PAY OUT"
Sheets("TWO PLUS").Range("AU5:AU14").SpecialCells(xlConstants).ClearContents
Sheets("TWO PLUS").Range("d6:d9").Value = ""
Sheets("TWO PLUS").Range("d14:d17").Value = ""
Sheets("TWO PLUS").Range("d22:d25").Value = ""
Sheets("TWO PLUS").Range("d30:d33").Value = ""
Sheets("TWO PLUS").Range("d38:d41").Value = ""
Sheets("TWO PLUS").Range("d46:d49").Value = ""
Sheets("TWO PLUS").Range("d54:57").Value = ""
Sheets("TWO PLUS").Range("d62:d65").Value = ""
Sheets("TWO PLUS").Range("d70:d73").Value = ""
Sheets("TWO PLUS").Range("d78:d81").Value = ""
Sheets("TWO MAN BEST BALL").Range("E7:E8").Value = ""
Sheets("TWO MAN BEST BALL").Range("E13:E14").Value = ""
Sheets("TWO MAN BEST BALL").Range("E21:E22").Value = ""
Sheets("TWO MAN BEST BALL").Range("E27:E28").Value = ""
Sheets("TWO MAN BEST BALL").Range("E35:E36").Value = ""
Sheets("TWO MAN BEST BALL").Range("E41:E42").Value = ""
Sheets("TWO MAN BEST BALL").Range("E49:E50").Value = ""
Sheets("TWO MAN BEST BALL").Range("E55:E56").Value = ""
Sheets("TWO MAN BEST BALL").Range("E63:E64").Value = ""
Sheets("TWO MAN BEST BALL").Range("E69:E70").Value = ""
Sheets("TWO MAN BEST BALL").Range("E77:E78").Value = ""
Sheets("TWO MAN BEST BALL").Range("E83:E84").Value = ""
Sheets("TWO MAN BEST BALL").Range("E91:E92").Value = ""
Sheets("TWO MAN BEST BALL").Range("E97:E98").Value = ""
Sheets("TWO MAN BEST BALL").Range("E105:E106").Value = ""
Sheets("TWO MAN BEST BALL").Range("E111:E112").Value = ""
Sheets("TWO MAN BEST BALL").Range("E119:E120").Value = ""
Sheets("TWO MAN BEST BALL").Range("E125:E126").Value = ""
Sheets("TWO MAN BEST BALL").Range("E132:E133").Value = ""
Sheets("TWO MAN BEST BALL").Range("E1329:E130").Value = ""




MsgBox "THE FORM HAS BEEN RESET."

VBA Code:
Option Explicit

Const VALIDATION_CELL = "B2" '<== change validation cell as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range(VALIDATION_CELL).Address Then
Sheets(Target.Value).Select
End If
End Sub
 
Upvote 0
If this is a direct copy and paste of what you have in the worksheet module, it is invalid, and I'm sorry to say a bit of a mess. You have no End Sub for your button handler; you have plain text copied from the post; and you have Option Explicit in the middle, which must be the first line in the module.

(When you post code I recommend that you select the code and then click the VBA button in the editor, which preserves the spacing and puts the code in a scroll window. Otherwise it is not very readable.)

This is what your module should look like. The code provided earlier in the thread goes at the top.:

VBA Code: 
Option Explicit

' !!! Note how indentation is preserved when using code tags!!!
Private Sub Worksheet_Change(ByVal Target As Range)

   Const VALIDATION_CELL = "B2" '<== change validation cell as needed.

   If Target.Address = Range(VALIDATION_CELL).Address Then
      Sheets(Target.Value).Select
   End If

End Sub

Private Sub CommandButton1_Click()

Dim warning
warning = MsgBox(Range("A1").Value & " WARNING !!!! WARNING !!! WARNING !!! This will reset the entire sheet. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, "Warning")
If warning = vbCancel Then Exit Sub

On Error Resume Next
Sheets("main").Range("b2").Value = "NAVIGATION"
Sheets("main").Range("d2").Value = "TEAM GEN"
Sheets("main").Range("E2").Value = "HISTORY"
Sheets("main").Range("F2").Value = "TWO PLUS"
Sheets("main").Range("G2").Value = "SKINS FEE"
Sheets("main").Range("H2").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J2").Value = "CTP'S FEE"
Sheets("main").Range("B4").Value = "CTP"
Sheets("main").Range("D4").Value = "GAME TYPE"
Sheets("main").Range("G4").Value = ""
Sheets("main").Range("H4").Value = ""
Sheets("main").Range("J4").Value = ""
Sheets("main").Range("b6").Value = "# 2"
Sheets("main").Range("b7").Value = "# 5"
Sheets("main").Range("b8").Value = "#11"
Sheets("main").Range("b9").Value = "#18"
Sheets("main").Range("c6:c9").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("main").Range("D6").Value = "STROKE ADJUSTMENT"
Sheets("main").Range("D7").Value = ""
Sheets("main").Range("D8").Value = "SKINS FRONT"
Sheets("main").Range("D9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("F6").Value = "PAID OUT"
Sheets("main").Range("F7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("F8").Value = "SKINS BACK"
Sheets("main").Range("F9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("G6").Value = "# PLAYERS"
Sheets("main").Range("G7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("G8").Value = "TOTAL SKINS"
Sheets("main").Range("G9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("H6").Value = "SKIN POOL"
Sheets("main").Range("H7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("H8").Value = "SKIN VALUE"
Sheets("main").Range("H9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J6").Value = "CTP POOL"
Sheets("main").Range("l7:ag9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("J8").Value = "CTP VALUE"
Sheets("main").Range("j9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("B11").Value = "CTP"
Sheets("main").Range("C11").Value = "ALT TEE"
Sheets("main").Range("D11").Value = "TEAM REQ"
Sheets("main").Range("E11").Value = "PLAYER"
Sheets("main").Range("F11").Value = "INDEX"
Sheets("main").Range("G11").Value = "HDCP"
Sheets("main").Range("H11").Value = "STROKES"
Sheets("main").Range("I11").Value = "QUOTA"
Sheets("main").Range("J11").Value = "ADJ QUOTA"
Sheets("main").Range("J11").Value = ""

Sheets("main").Range("c6:c9").ClearContents
Sheets("main").Range("aj7:an7").ClearContents


Sheets("main").Range("G12:J51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("b12:b51").Value = ""
Sheets("main").Range("c12:c51").Value = ""
Sheets("main").Range("d12:d51").Value = ""
Sheets("main").Range("e12:e51").Value = ""
Sheets("main").Range("f12:f51").Value = ""
Sheets("main").Range("L2").Value = "TEE"
Sheets("main").Range("N2").Value = "RATING"
Sheets("main").Range("P2").Value = "SLOPE"
Sheets("main").Range("R2").Value = "COR FAC"
Sheets("main").Range("T2").Value = "MAIN"
Sheets("main").Range("Z2").Value = "PLAYING CONDITIONS"
Sheets("main").Range("AF2").Value = "BUNKERS"
Sheets("main").Range("L4").Value = "SELECT TEE"
Sheets("main").Range("N4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("P4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("R4").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("T4").Value = "SELECT COURSE"
Sheets("main").Range("Z4").Value = "PLAYING CONDITIONS"
Sheets("main").Range("AF4").Value = "BUNKER RELIEF"
Sheets("main").Range("L7:G9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("Z11").Value = "SKINS"
Sheets("main").Range("M11").Value = "FRONT"
Sheets("main").Range("V11").Value = "IN"
Sheets("main").Range("W11").Value = "BACK"
Sheets("main").Range("AF11").Value = "OUT"
Sheets("main").Range("AG11").Value = "TOTALS"
Sheets("main").Range("AH11").Value = "POINTS"
Sheets("main").Range("L12:U51").Value = ""
Sheets("main").Range("V12:V51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("W12:AE51").Value = ""
Sheets("main").Range("AF12:AH51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ4").Value = "POINT / PLACE"
Sheets("main").Range("AJ6").Value = "# PLACES PAID"
Sheets("main").Range("AK6").Value = "% FOR 1ST PLACE"
Sheets("main").Range("AL6").Value = "% FOR 2ND PLACE"
Sheets("main").Range("AM6").Value = "% FOR 3RD PLACE"
Sheets("main").Range("AJ7:AM7").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ8").Value = "# OF PLAYERS"
Sheets("main").Range("AK8").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AL8").Value = "PLAYERS IN POSITIVE"
Sheets("main").Range("AM8").Value = "$ PER POINT"
Sheets("main").Range("AN8").Value = "PD OUT"
Sheets("main").Range("AJ9:AN9").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("AJ11").Value = "PTS vs QUOTA"
Sheets("main").Range("AK11").Value = "PLACE PAY OUT"
Sheets("main").Range("AL11").Value = "POINT PAY OUT"
Sheets("main").Range("AM11").Value = "SKINS PAY OUT"
Sheets("main").Range("AN11").Value = "# SKINS"
Sheets("main").Range("AJ12:AN51").SpecialCells(xlConstants).ClearContents
Sheets("main").Range("i12:i51").SpecialCells(xlConstants).ClearContents

Sheets("TEAM GENERATOR").Range("l2").Value = "TEAMS"
Sheets("TEAM GENERATOR").Range("I6").Value = "TEAM 1"
Sheets("TEAM GENERATOR").Range("M6").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O6").Value = "TEAM 2"
Sheets("TEAM GENERATOR").Range("S6").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I13").Value = "TEAM 3"
Sheets("TEAM GENERATOR").Range("M13").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O13").Value = "TEAM 4"
Sheets("TEAM GENERATOR").Range("S13").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I20").Value = "TEAM 5"
Sheets("TEAM GENERATOR").Range("M20").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O20").Value = "TEAM 6"
Sheets("TEAM GENERATOR").Range("S20").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I27").Value = "TEAM 7"
Sheets("TEAM GENERATOR").Range("M27").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O27").Value = "TEAM 8"
Sheets("TEAM GENERATOR").Range("S27").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("I34").Value = "TEAM 9"
Sheets("TEAM GENERATOR").Range("M34").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("O34").Value = "TEAM 10"
Sheets("TEAM GENERATOR").Range("S34").Value = "HDCP"
Sheets("TEAM GENERATOR").Range("i7:i10").Value = ""
Sheets("TEAM GENERATOR").Range("i14:i17").Value = ""
Sheets("TEAM GENERATOR").Range("i21:i24").Value = ""
Sheets("TEAM GENERATOR").Range("i28:i31").Value = ""
Sheets("TEAM GENERATOR").Range("i35:i38").Value = ""
Sheets("TEAM GENERATOR").Range("o7:eek:10").Value = ""
Sheets("TEAM GENERATOR").Range("o14:eek:17").Value = ""
Sheets("TEAM GENERATOR").Range("o21:eek:24").Value = ""
Sheets("TEAM GENERATOR").Range("o28:eek:31").Value = ""
Sheets("TEAM GENERATOR").Range("o35:eek:38").Value = ""
Sheets("Team Generator").Range("m7:m39").SpecialCells(xlConstants).ClearContents
On Error Resume Next
Sheets("Team Generator").Range("s7:S39").SpecialCells(xlConstants).ClearContents
On Error GoTo 0

Sheets("scramble").Range("d6:d9").Value = ""
Sheets("scramble").Range("d14:d17").Value = ""
Sheets("scramble").Range("d22:d25").Value = ""
Sheets("scramble").Range("d30:d33").Value = ""
Sheets("scramble").Range("d38:d41").Value = ""
Sheets("scramble").Range("d46:d49").Value = ""
Sheets("scramble").Range("d54:d57").Value = ""
Sheets("scramble").Range("d62:d65").Value = ""
Sheets("scramble").Range("d70:d73").Value = ""
Sheets("scramble").Range("d78:d81").Value = ""
Sheets("scramble").Range("d86:d89").Value = ""
Sheets("scramble").Range("d94:d97").Value = ""

On Error Resume Next
Sheets("TWO PLUS").Range("AU4").Value = "PAY OUT"
Sheets("TWO PLUS").Range("AU5:AU14").SpecialCells(xlConstants).ClearContents
Sheets("TWO PLUS").Range("d6:d9").Value = ""
Sheets("TWO PLUS").Range("d14:d17").Value = ""
Sheets("TWO PLUS").Range("d22:d25").Value = ""
Sheets("TWO PLUS").Range("d30:d33").Value = ""
Sheets("TWO PLUS").Range("d38:d41").Value = ""
Sheets("TWO PLUS").Range("d46:d49").Value = ""
Sheets("TWO PLUS").Range("d54:57").Value = ""
Sheets("TWO PLUS").Range("d62:d65").Value = ""
Sheets("TWO PLUS").Range("d70:d73").Value = ""
Sheets("TWO PLUS").Range("d78:d81").Value = ""
Sheets("TWO MAN BEST BALL").Range("E7:E8").Value = ""
Sheets("TWO MAN BEST BALL").Range("E13:E14").Value = ""
Sheets("TWO MAN BEST BALL").Range("E21:E22").Value = ""
Sheets("TWO MAN BEST BALL").Range("E27:E28").Value = ""
Sheets("TWO MAN BEST BALL").Range("E35:E36").Value = ""
Sheets("TWO MAN BEST BALL").Range("E41:E42").Value = ""
Sheets("TWO MAN BEST BALL").Range("E49:E50").Value = ""
Sheets("TWO MAN BEST BALL").Range("E55:E56").Value = ""
Sheets("TWO MAN BEST BALL").Range("E63:E64").Value = ""
Sheets("TWO MAN BEST BALL").Range("E69:E70").Value = ""
Sheets("TWO MAN BEST BALL").Range("E77:E78").Value = ""
Sheets("TWO MAN BEST BALL").Range("E83:E84").Value = ""
Sheets("TWO MAN BEST BALL").Range("E91:E92").Value = ""
Sheets("TWO MAN BEST BALL").Range("E97:E98").Value = ""
Sheets("TWO MAN BEST BALL").Range("E105:E106").Value = ""
Sheets("TWO MAN BEST BALL").Range("E111:E112").Value = ""
Sheets("TWO MAN BEST BALL").Range("E119:E120").Value = ""
Sheets("TWO MAN BEST BALL").Range("E125:E126").Value = ""
Sheets("TWO MAN BEST BALL").Range("E132:E133").Value = ""
Sheets("TWO MAN BEST BALL").Range("E1329:E130").Value = ""



MsgBox "THE FORM HAS BEEN RESET."

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Question about Drop down list
Replies
3
Views
228
RoryA
RoryA
C
  • Question
Dependent Drop Down Help, Excel 2010
Replies
5
Views
141
Akuini
Akuini
R
  • Solved
adapt drop down list after selection is made
Replies
7
Views
200
Peter_SSs
Peter_SSs
K
  • Question
Looking to create a drop-down list where selecting a name brings me to a specific cell containing that name
Replies
5
Views
102
SanjayGMusafir
SanjayGMusafir
M
  • Solved
Drop down list with hyperlinks
Replies
4
Views
216
munchems
M

Forum statistics

Threads
1,214,947
Messages
6,122,411
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top