Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2004
    North Plainfield, New Jersey, USA
    Thanked 0 Times in 0 Posts

    MsgBox (office xp)

    I am trying to get a warning message to pop up if multiple parameters are used. I have a workbook with several worksheets, each with several drop down lists. I want a msgbox to pop up if I choose a specific item on a drop down on one sheet and a then enter number less than 7.75 in a cell on another sheet. Is this possible. I have tried several small macros that either crash or son't do anything. one example is:
    Function WidthError(SlideType)
    SlideType = Hettich

    If Width < 7.75 Then MsgBox "Drawer is too narrow for Hettich Slides", , "Warning"

    End If
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: MsgBox (office xp)

    Give the cell that contains the result of the dropdown list a name: select the cell, then type the name in the Name Box on the left hand side of the formula bar. Say that you name it SlideType.
    Next, select the cell in which the number will be entered.
    Give this cell a name too, for example Number.
    Select Data | Validation...
    Select Custom from the Allow dropdown list.
    Enter the following formula:

    =OR(Number > = 7.75,SlideType < > "Hettich")

    Note: I typed spaces in this formula because some browsers display it incorrectly without the spaces; remove the spaces if you copy the formula.

    Activate the Error Alert tab of the Validation dialog and enter the error message you want to display. The Style should be set to Stop. Finally, click OK.

    If SlideType is anything else than "Hettich", there is no restriction on Number, but if SlideType is "Hettich", Number must be at least 7.75.

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    San Jose, California, USA
    Thanked 0 Times in 0 Posts

    Re: MsgBox (office xp)

    If you want a macro instead of a formula (which can do the message box and other things try this:
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range

    ' item selected from list
    Set rng = ThisWorkbook.Worksheets("Source").Range("sourcedat a")

    If Target.Value < 7.75 Then
    MsgBox "Drawer width is too narrow, select another size."
    Application.EnableEvents = False
    Target.Value = 0
    Application.EnableEvents = True

    ' take user back to the input list
    End If

    End Sub</pre>

    rng is the variable pointing to the selected number in the list (on a sheet other than this one). I don't do anything with it in this code but I assume you have some relationship between what's selected on that source sheet and the other sheets.I named these cells so it's easier to read the code.

    I have a real sample in the attached workbook. Sheet 'source' has your list of numbers. The other sheet has the data entry field which tests for 7.75 and displays message. I do not know the relationship between the number selected in the list and this 7.75 on the next sheet so you can add that part. This just tests for 7.75 entered in the data sheet.


Posting Permissions

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