+ Reply to Thread
Results 1 to 3 of 3

How to set-up dropdown list default after changing worksheet using vba

Hybrid View

  1. #1
    Registered User
    Join Date
    01-02-2017
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    6

    How to set-up dropdown list default after changing worksheet using vba

    I have the following vba code set-up to change the worksheet upon selecting a dropdown list item:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("C6")) Is Nothing Then
    Application.ScreenUpdating = False
    Sheets(Range("AB13").Value).Select
    Application.ScreenUpdating = True
    End If
    End Sub

    I would like the dropdown list to show another item (the same one every time) upon returning to the worksheet.

    What code would accomplish this and where do i insert it (before the code above, somewhere in between the code above or after the code above)?

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: How to set-up dropdown list default after changing worksheet using vba

    Hi,

    You would require the Worksheet_Activate event. Presuming you do not wish to run the Change event again when the value is reset, you might use code like this
    Private Sub worksheet_activate()
    If Range("C6").Value2 <> "your default value" then
    application.enableevents = false
    range("C6").value2 = "your default value"
    application.enableevents = true
    end if
    end sub
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    01-02-2017
    Location
    Toronto, Canada
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    6

    Re: How to set-up dropdown list default after changing worksheet using vba

    Worked like a charm. Thanks!

+ 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. Drop down list with changing default values
    By lmanea in forum Excel General
    Replies: 3
    Last Post: 09-13-2016, 06:56 AM
  2. [SOLVED] Drop down list - changing default
    By Steven811 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-04-2016, 02:20 AM
  3. [SOLVED] Default in Dropdown list
    By rizmomin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-15-2015, 12:37 PM
  4. Replies: 2
    Last Post: 02-24-2015, 02:58 AM
  5. Default text to show in dropdown list
    By Data2link in forum Excel General
    Replies: 2
    Last Post: 02-13-2015, 02:28 PM
  6. Changing the font used in a dropdown list
    By rarice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2007, 06:24 AM
  7. [SOLVED] Changing default worksheet tab fonts
    By 1940LaSalle in forum Excel General
    Replies: 4
    Last Post: 12-29-2005, 03:30 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