1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: excel validation list autodisplay first item

Discussion in 'Business Applications' started by Willum, Apr 19, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    Hi all,

    I was wondering. I have an Excel sheet in which people can select some few things.
    For example Type, Gender.

    Depending on what's selected here, another validation list is filled in with values (male gives other values then female).

    Now I would like that when a Type and Gender are selected, the first item from the new to select values is displayed automatically.

    So is it possible that a validation list automatically displays the first item instead of staying blank?

    I've searched the internet for a solution, but I can't find any.

    Thanks in advance.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I don't know how your vba knowledge is, but you could add code to the sheet that when the cell containing the gender value is 'm then certain other cells get activated or filled automatically, same with 'f''

    If you could be more specific and maybe attach a simple sample file of what you have, and what you want then we could help you on the way.
    There really are quite a lot of sites where you can find almost anything related to vba and you can even download examples.
     
  3. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    I'll upload an example in a few minutes. Nice to see another Dutchman here.

    The example is very simple, It's only about the part where I've put the extra explenation.

    my VBA skills ar not so good. I don't know how to write a code myself, but when I have a code, I can modify it (logical thinking)
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    That's the way I picked it up, record macro's and then editting them.
    I just picked up your file and will take a look at it before bed-time.
    Groetjes,
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I don't think I grasp the idea.
    What do you want / need when you select male? (or female for that matter)
    Just one value or the list for male to be active? That's what happens now.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I got the idea, I just didn't read it all.
    I'll take a look later, I think some simple vba code will do the job for uou.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    This is the code in the sheet:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Address = "$C$4" Then
    Select Case Range("C4").Value
    Case Is = "male"
    Range("MYRANGE").Value = Range("MALE").Value
    Case Is = "female"
    Range("MYRANGE").Value = Range("FEMALE").Value
    Case Else
    Range("MYRANGE").Value = ""
    End Select
    Range("D17").Value = Range("D10").Value
    Range("D17").Select
    End If

    End Sub

    It's quick and dirty but I hope it puts you on your desired track.
     

    Attached Files:

  8. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    hmm that's not going to work for me in my original sheet.

    In cells F9 till F13 are values shown, which change on what combo is selected in Cell A1, B1 and C1.
    In Cell B20 I have a data validation list which has the range of cell F9 - F13. But this Cell is empty on start, but I like it to display Cell F9 standard (but with the option of selecting another value from the range F9 - F13.

    Maybe I didn't explain it that clear.

    So the list in B20 is created from the cells F9 - F13, but how can I automatically display Cell F9 at the start (instead of B20 beïng blank)
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    The sheet you sent me did not contain data in the cells you mention
    Maybe you should send that one.
    You explained enough but what I returned to you is a copy of the sheet you sent and A1, B1 and C1 are empty as are F9 till F13
     
  10. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    True, but the original file does.
    But the original file i can't place here because of specific data.

    Let me say it in this way.
    This example is not in the file I put here.

    I would like cell B20 to have a data validation list which source is cell F9-F13. But normally this validation list is empty at the beginning (none is filled in automatically, until you select one of the options (which can be find in F9-F13))
    Is it possible that B20 has a validation list (F9-F13), but that F9 is filled in automatically. So you don't need to select F9 manually, but if you like, you could change this value to another value in the list.

    So in fact we don't even need a file at all I think ;-) Just if there is a way to automatically let it show the first item of the list instead of blank.
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I named F9-F13 THELIST and have it as datavalidation for B20

    I added
    Private Sub Worksheet_Activate()
    Range("B20").Value = Range("F9").Value
    End Sub

    So that when the sheet is activated B20 is filled with data from F9
    If you want you can add a check If Range("B20").value = "" then Range("B20").Value = Range("F9").Value
    to avoid changing the value already in B20 when activating the sheet again.

    Let me know if you need more help, maybe there is another way but this one works for me in other situations.
     

    Attached Files:

  12. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    If I see the code, I think it should have to work. But it didn't.

    I gave F9-F13 the name "start". The validation list in cel B20 = "start" (this works).
    But you got the code running on Worksheet_Activate()
    Is there a possiblitiy to change this to if a specific cell changes ( if cel a1 = active or changed or wathever is possible)

    So I think I only need tot change Worksheet_Activate() to cell a1 activate.

    Now it doesn't work, because the macro's are shut down when opening the file.
     
  13. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    whoot, got it working:)
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    Range("B20").Value = Range("F9").Value
    End If

    End Sub
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    If you save this file as a vbs script in the same folder as your Excel sheet
    and make sure that "<excel filename .xls>" contains the exclefilename.xls
    You can run the Excel sheet including macro's even if Macro security is set high.
    The default macro is set to "Auto_Open" but if you enter another existing macro name or function it will also work

    Public GetAbsolutePath, GetTheParent, myCfgFile
    Dim objExcel, f, objFso
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objExcel = CreateObject("Excel.Application")
    'GetTheScriptFullName = WScript.ScriptFullName 'Returns path of the script being called.
    'GetTheScriptName = Wscript.ScriptName 'Returns the name of the script
    GetAbsolutePath = objfso.GetAbsolutePathName(Wscript.ScriptName)
    GetTheParent = Objfso.GetParentFolderName(WScript.ScriptFullName) 'Returns the parentfolder of the Path/File specified

    fname = GetTheParent & "<excel filename .xls>"
    If objFso.FileExists(fname) Then
    On error resume next
    objExcel.Workbooks.Open(fname)
    On error goto 0
    objExcel.Visible = True
    On error resume next
    objExcel.Run "Auto_Open"
    set objExcel=Nothing
    Set objFso=Nothing
    Else
    wscript.echo fname & " does not exist!"
    End If
    wscript.quit

    This is a security leak in VBA.
     
  15. Willum

    Willum Thread Starter

    Joined:
    Jun 12, 2008
    Messages:
    137
    hmm this I don't get.

    I use this piece of code in my VB

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$37" Then
    Range("B39").Value = Range("F9").Value
    Range("B44").Value = Range("F16").Value
    Range("B49").Value = Range("F23").Value
    End If
    End Sub

    I've copied the script you wrote into Textblock (or wathever it's called in English) and paste it there. I've changed the .xls to the correct name. But what do I need to do with the Auto_Open? Leave it Auto_Open or change it to something else? I've noticed that with the code above I first need to allow macro's, so would be nice if this code prevents that, because it's going to be used by a few people who don't know that much about computers ;)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917865

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice