+ Reply to Thread
Results 1 to 5 of 5

Linking data validation drop down selection to open userform

  1. #1
    Registered User
    Join Date
    03-28-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Linking data validation drop down selection to open userform

    Hi there,

    I'm new-ish to VBA and for most of my problems i have been able to find answers in threads such as these.. untill now!

    I have a main worksheet (Sheet1) which is a type of 'tracker' where users use a data validation drop-down menu to track the progress of different on-going projects (e.g. not started, in progress, postponed, in dispute etc etc.). For each of these projects i have separate individual worksheets where more detailed information is documented for each stage of the project. I have created a user form for each of these worksheets so that the data is presented in a more thorough but consistent way. As well as the additional information, the userform has the same drop-down choices as the main worksheet. I've attached an image of a basic mock-up to try and illustrate what i am trying to do.

    What i'm trying to achieve:

    When the user changes the selection in the drop-down menu in the main worksheet i want it to automatically go to the relevant worksheet (e.g. project1) and open up the userform so that the user records why the status of the project has changed. Ideally, it will also select the same choice in the userform as that made in the drop-down list.

    Appreciate any help on this!

    Thanks in advance,

    Stew


    Project Example.PNG

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,046

    Re: Linking data validation drop down selection to open userform

    Copy the code below, right-click the tab of Sheet1, and paste the code into the window that appears. You will need to modify the code to match the names of the userforms and the control that you want your value to appear in, neither of which you gave in your post.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    03-28-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Linking data validation drop down selection to open userform

    Hi Bernie,

    Thanks so much for your help! i've adapted the code and it works well. From what i gather, the code works by using the target value as a string in column 4 (in my code below) and then if there is a worksheet with the same name as that string, any data entered in that row will then open the worksheet and the userform. Is that pretty much right?

    I have a few questions which you may be able to help me with:

    1. There are certain cells in each row that will be entered manually by the user (i.e. not in a drop down box), and when they do i do not want it to link to the project worksheet/open userform. For example, would you be able to apply the code to columns greater than column 7 (i.e. in columns A:F the user can type in data and it will not link to the project worksheet)?

    2. Party linked to No.1 is that if the user inserts a new column, say in column 1, the strName = cells (target.row, 4) in my code below will not sutomatically change to strName = cells (target.row, 5) - is there a way of locking this column number?

    3. Sometimes the user will need to delete an entry in the tracker, but at present, when the delete button is pressed it links to the project worksheet/userform. Is there a way of being able to press delete and the code not running?

    4. In the example in my post above, each stage of the project had a different year (2014, 2015, 2016) - when a change is made in the tracker is there a way to link the date in the corresponding column above so that it is entered into the date.textbox in the userform?


    If you can help on any of the above questions it would be greatly appreciated!

    Thanks again,

    Stew



    My code so far (more projects will be added when i get it right):



    Private Sub Worksheet_Change(ByVal Target As Range)
    'link a change in tracker to open userform in relevant project worksheet
    Dim strName As String
    'set column which will use to lookup name of worksheet to link to

    strName = Cells(Target.Row, 4).Value
    On Error GoTo NoGood

    'stop other code working while we run this code

    Application.EnableEvents = False
    Sheets(strName).Activate
    Select Case strName
    Case "TRAFL1 "

    Rec_Tracker_SendReceive.Show

    End Select

    'allow other code to work again

    NoGood:
    Application.EnableEvents = True
    End Sub

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,046

    Re: Linking data validation drop down selection to open userform

    Thanks so much for your help! i've adapted the code and it works well. From what i gather, the code works by using the target value as a string in column 4 (in my code below) and then if there is a worksheet with the same name as that string, any data entered in that row will then open the worksheet and the userform. Is that pretty much right?
    Yes - that is correct.

    2. Party linked to No.1 is that if the user inserts a new column, say in column 1, the strName = cells (target.row, 4) in my code below will not sutomatically change to strName = cells (target.row, 5) - is there a way of locking this column number?
    This one first - name a cell in the name column that you use - say "NameCell", and then use code like

    Please Login or Register  to view this content.
    And as columns are inserted or deleted, you code will continue to work properly

    1. There are certain cells in each row that will be entered manually by the user (i.e. not in a drop down box), and when they do i do not want it to link to the project worksheet/open userform. For example, would you be able to apply the code to columns greater than column 7 (i.e. in columns A:F the user can type in data and it will not link to the project worksheet)?
    Start your code with this
    Please Login or Register  to view this content.
    and then name a cell "StartCol" in the first column you want this to work on and use this as the second line
    Please Login or Register  to view this content.
    3. Sometimes the user will need to delete an entry in the tracker, but at present, when the delete button is pressed it links to the project worksheet/userform. Is there a way of being able to press delete and the code not running?
    Start your code with
    Please Login or Register  to view this content.
    and finish it with
    Please Login or Register  to view this content.
    4. In the example in my post above, each stage of the project had a different year (2014, 2015, 2016) - when a change is made in the tracker is there a way to link the date in the corresponding column above so that it is entered into the date.textbox in the userform?
    Depends on the specifics, but code like this will work if you name the column with the dates "DateCol"
    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 04-10-2014 at 12:32 PM.

  5. #5
    Registered User
    Join Date
    03-28-2014
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Linking data validation drop down selection to open userform

    Thanks for your help Bernie - i've played around and it's all working now.

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Userform Pop up on Data validation selection
    By RichardJSigKits in forum Excel General
    Replies: 0
    Last Post: 03-25-2014, 12:52 PM
  2. Replies: 4
    Last Post: 08-01-2013, 09:56 AM
  3. Replies: 6
    Last Post: 07-29-2013, 07:31 AM
  4. Data validation drop down list with several selection
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:48 AM
  5. Data Validation: Need to restrict drop-down selection
    By splenguin in forum Excel General
    Replies: 5
    Last Post: 09-08-2009, 03:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1