none
Is Not Empty Assistance RRS feed

  • Question

  • Greetings and salutations J

    I am seeking some assistance with the below. The scenario is this…I have a drop-down that I am trying to make as a requirement if there is an entry/data in another cell.

    Example: If cell G24 contain data, the drop-down located in cell Q24 will be a requirement. If cell G24 is blank, then the drop-down in Q24 would not be required.

    I do need it to look at each row independently. Meaning I don’t think a loop would be feasible; as there may be an entry in cell G24 making Q24 required, but the next series rows/cell G25 may be empty, thus making the related drop-down (Q25) not required.

    Here is what I have come up with thus far. This works ok, but when it gets to the second set/series (G25/Q25), even if cell G25 is blank it is still making the drop-down (Q25) a requirement?

    If Not IsEmpty(Range("G24").Value) = True Then   ‘1<sup>st</sup> set

    r = MsgBox("There is equipment listed in this request. Is the Item located in current inventory? Please select Yes or No", _

                    vbQuestion + vbOKOnly, "Error!") 'from the DropDown

    If r = vbOK Then

    Call ClearSig21_Click

    ActiveSheet.Range("Q24").Select 'DropDown

    Exit Sub

    End If               

    Else

    ActiveSheet.Range("A32").Select       

    End If

           

    '''''''''''''''''''''''''''''''''''''''''''''''''''

    If Not IsEmpty(Range("G25").Value) = True Then  ‘2<sup>nd</sup> set

    r = MsgBox("There is equipment listed in this request. Is the Item located in current inventory? Please select Yes or No", _

                    vbQuestion + vbOKOnly, "Error!")    'from the DropDown

    If r = vbOK Then

    Call ClearSig21_Click

    ActiveSheet.Range("Q25").Select    'DropDown

    Exit Sub

    End If

    Else

    ActiveSheet.Range("A32").Select

    End If

       

        '''''''''''''''''''''''''''''''''''''''''''''''''''   

    If Not IsEmpty(Range("G26").Value) = True Then  ‘3<sup>rd</sup> set

    r = MsgBox("There is equipment listed in this request. Is the Item located in current inventory? Please select Yes or No", _

    vbQuestion + vbOKOnly, "Error!") 'from the DropDown

    If r = vbOK Then

    Call ClearSig21_Click

    ActiveSheet.Range("Q26").Select 'DropDown

    Exit Sub

    End If

    Else

    ActiveSheet.Range("A32").Select

    End If

    Hope I explianed clear enough. Should you need any clarifications pleaee let me know.  Otherwise, if anyone has any ideas on how to make this work or maybe even an alternative solution I would be most greateful. Thanks in advance for any assistance you may be able to provide J

    Wednesday, April 6, 2016 6:44 PM

Answers

  • >>>if anyone has any ideas on how to make this work or maybe even an alternative solution I would be most greateful. Thanks in advance for any assistance you may be able to provide

    According to your description, please correct me if I have any misunderstandings on your question, you could create and remove data validation from a cell in Excel with Macro.
    Create data validation Dropdown list:
    Sub DropDownList()
        Range("B1").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=$G$1:$G$4"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    End Sub
    Remove data validation:
    Range("B1").Validation.Delete
    In addition could you upload your Excel file on OneDrive, that will help us reproduce and resolve your issue.

    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:10 PM
    • Marked as answer by David_JunFeng Sunday, April 17, 2016 2:23 PM
    Thursday, April 7, 2016 2:10 AM