PDA

View Full Version : find worksheet based on selection from dropdown list



RatherBeRidi
06-27-2012, 07:47 AM
I am using Excel to collect data from 36 groups. Their name and ID will be selected from a drop down list with the source a named rangetitled PO_NameID. The PO_NameID range is two columns (Name, ID). All groups will answer the same set of questions on sheet 1, the first being the name and ID. There will be 36 additional sheets specific to each group that I need completed. I'm thinking I can have command button at the end of sheet 1 that would "find" the ID that was selected and activate the corresponding sheet (ID is sheet name). I'm not sure how to start the code for this. Can some help me get started? Thanks!

Bob Phillips
06-27-2012, 08:58 AM
Post the actual workbook.

RatherBeRidi
06-27-2012, 10:57 AM
Thanks XLD. I've attached a file that is a very rough draft. I'm trying to make sure I can accomplish what I have in mind before creating all of the worksheets. Sheet1 will be the "form" that the users will see. Their entries will be copied to the DATA sheet so that I can import the information into Access later. When completed, each will have only one row of data on the DATA sheet and the corresponding SubGroup sheet.

Thank you.

RatherBeRidi
06-27-2012, 10:59 AM
Oops, looks like I missed the file. Hopefully it's now uploaded.

Bob Phillips
06-27-2012, 02:47 PM
Add this code to the Sheet1 code module

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B2")) Is Nothing Then

Worksheets(Target.Offset(0, 1).Value).Activate
End If
End Sub

RatherBeRidi
06-28-2012, 06:32 AM
Wow - that is slick. But, not quite what I need. The user needs to complete all of the questions on sheet1 before going to the corresponding subgroup page, which is why I thought I would need a command button. Another option would be if the answer to the final question (not on the file I uploaded) is No, they are taken to the subgroup page. If the answer is Yes, another question would be enabled. Whichever is easier is fine. Thanks.

Bob Phillips
06-28-2012, 06:44 AM
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("B2")) Is Nothing Then

If Me.Range("C5").Value <> "" And _
Me.Range("C8").Value <> "" And _
Me.Range("C10").Value <> "" And Me.Range("D10").Value <> "" And _
Me.Range("C13").Value <> "" And _
Me.Range("C14").Value <> "" And _
Me.Range("C15").Value <> "" Then

Worksheets(Target.Offset(0, 1).Value).Activate
Else

MsgBox "All fields must be completed!", vbExclamation, "PO Data"
End If
End If
End Sub

RatherBeRidi
06-28-2012, 07:31 AM
Thanks for such a quick reply. I'm sorry for the confusion. Every question may not be required. But, the last one that I forgot to include in the copy I uploaded will be. (You could add Q5 with a Yes/No response.) Your code is triggered when a selection is made in B2, which is the first thing the user selects. I need to have the trigger at the end of the questions. I think a command button to open the worksheet that correspondes to the entry in C2 would be best, but I am certainly open to other suggestions. One more point, I plan to hide all of the subgroup worksheets, but I think I can unhide them in the code.

RatherBeRidi
06-28-2012, 12:07 PM
I've had a couple of changes thrown at me. Name is now in C2 instead of B2, and ID is in D2 instead of C2. Essentially, it is the same problem. How to activate the worksheet that corresponds to the value in D2. If the response to the final question (D95) is No, the subgroup worksheet that corresponds to the value in D2 is activated. If the response to D95 is yes, D96 is unlocked for response. I've tried to edit the code you provided, but I am missing something. If I select No in D95 the corresponding worksheet does not open and D96 remains locked. If I select Yes in D95, D96 is unlocked and I can make a selection. Can you show me where I went wrong, please?


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D95")) Is Nothing Then

If Me.Range("D95").Value = "No" Then

If Not Intersect(Target, Me.Range("C2")) Is Nothing Then
Worksheets(Target.Offset(0, 1).Value).Activate
End If
Else
ActiveSheet.Unprotect
Me.Range("D96").Locked = False
ActiveSheet.Protect
End If
End If
End Sub