Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inputbox on dataentry (2003)

    Hi,

    I have two combo boxes, I would you an inputbox to pop up on update of the second combo if a certain selection in the first combo is selected

    Thanks
    Regards
    Gerbil (AKA Kevin)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputbox on dataentry (2003)

    Could you be more specific? Perhaps attach a sample file with what you have and what you want done exactly...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    Yes sorry, I was rushing to a meeting when I listed it.

    Cell A1:A9 contain a drop down list with names
    Cell B1:B9 contain a drop down list with order possibility's

    I would like a way that when a user selects any of the order options in cells B1:B9 An input box will appear with a message, but only if a A1:A9 in the corresponding coloum = "TEST"

    So

    A1 = Kevin
    B1 = Order A

    Output = No message box to appear

    but...

    A1 = John
    B1 = Order A (or any other order)

    Output = 'You are not allowed to order this item'

    Thanks
    Regards
    Gerbil (AKA Kevin)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputbox on dataentry (2003)

    What kind of drpdown list, there are several types. It would be helpful if you provided a sample workbook...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    I have attached my sample, at the moment when you select the cell next to the name Lindsey there is a comment box I want it to work so that when any of the name selection cells = Lindsey and a selection is made to the combo on the right of the name combo a message box will appear.

    Many thanks
    Attached Files Attached Files
    Regards
    Gerbil (AKA Kevin)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputbox on dataentry (2003)

    I am still unclear what you want. Each post you have done seems to asking for something different which confuses me even more...

    I am just making a big guess here but are you after something like MS MVP Debra Dagliesh does in Data Validation -- Dependent Lists?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    Hi again, sorry after reading my own posts I realise I have not been clear. Maybe this will help

    A1:A10 = Peoples names (it is a combo that gets its values from a lookup else where in the spreadsheet.
    B1:B10 = Menu choices (Once a user has selected their name from any of A1:A10 they can then select their food choice)

    B1:B10 I want this to have an onupdate event if the value in A1:A10 = Lindsey

    I no the below code will not work but maybe writting this will help me explain.

    Sub B1:B10_OnUpdate()
    If Range("A1:A9").value = "Lindsey" Then
    MsgBox"No children's size available for this option", vbinformation
    End If
    End Sub

    As far as I can tell you can't use onupdate events like in access, but I'm sure there is a way of doing it.

    Many thanks
    Regards
    Gerbil (AKA Kevin)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputbox on dataentry (2003)

    That is not how your sample file is setup....

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    I know I cant figure out how to do it thats why!
    Regards
    Gerbil (AKA Kevin)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Inputbox on dataentry (2003)

    You either need to describe what you want done using the sample file already attached, or attach a sample file that matches what you are describing you currently have...

    Steve

  11. #11
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    There are a couple of ways one could go with this.
    You could put this in the sheets code module. (If the lay-out has changed from the attachment, ranges will need to be adjusted.)

    <code>Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim altMenuMessage As String, altPrice As Double, altMenuItem As String
    altMenuItem = "Chicken Strips with Chips"
    altPrice = 3.29
    altMenuMessage = "There is no child's menu avaliable." & vbCr
    altMenuMessage = altMenuMessage & "Would you like to order the " & altMenuItem & " ("
    altMenuMessage = altMenuMessage & Format(altPrice, "currency") & ")?"
    With Target
    If .Cells.Count = 1 And (.Column = 10 Or .Column = 12) Then
    If .EntireRow.Range("I1").Value = "Lindsey" _
    And .EntireRow.Range("J1")=vbNullString _
    And .EntireRow.Range("L1")=vbNullString Then
    If MsgBox(altMenuMessage, vbYesNo) = vbYes Then
    .Value = altMenuItem
    .Offset(0, 1) = altPrice: Rem Indicated line
    End If
    End If
    End If
    End With
    End Sub</code>





    However, overwriting the formula in the price column makes changing Lindsey's mind a problem.
    What you could do about that is:

    1) Put "Chicken Strips With Chips" in C8 and 3.29 in G8. Changing the font color to white in both cells will hide this. Since these cells are outside the range the Validation draws from, it won't add Chicken Strips to the others's menus.

    2 ) Change the range in all the VLOOKUPS to =IF(J3>"",VLOOKUP(J3,$C$2:$G$17,5,FALSE),"0.00"). Since no Starter has the same name as a Main Course this would cause no problems.

    3) Remove the Indicated Line from the VB routine.

    I hope this helps.

  12. #12
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox on dataentry (2003)

    Many many thanks, this works as I had hoped
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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