Want to be able to enter free text in a field if an IF statement is false?
Results 1 to 4 of 4

Thread: Want to be able to enter free text in a field if an IF statement is false?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2014
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Want to be able to enter free text in a field if an IF statement is false?

    Hello

    I have a spreadsheet and would like an IF statement (if possible) that allows the user to enter free text if the IF statement is false.

    So I need it to say If E4=Mobile Shredder and H4=PS then select from drop down list. If not, then enter free text.

    Any ideas? Is it possible?

    Thank you in advance.

    Sophie

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,513
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Want to be able to enter free text in a field if an IF statement is false?

    A cell can have a formula or you can enter text. If you have a IF statement in a cell any text entered will overwrite the formula. You could use a change event to put a data validation list in the cell or remove it so any text can be entered based on your criteria.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    Board Regular
    Join Date
    Oct 2014
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Want to be able to enter free text in a field if an IF statement is false?

    Thanks for the reply.

    A change event? Can you explain further?

    Thanks for your help.

  4. #4
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,513
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Want to be able to enter free text in a field if an IF statement is false?

    Code can be entered to run when a cell is changed.

    https://msdn.microsoft.com/en-us/vba...ge-event-excel

    This code would check what cell was changed. If it is E4 or H4. If the cell is one if the two it tests what is in the cell and either sets up data validation or cleared data validation to allow any text to be entered. I assumed A4 was the cell.
    Right click on the tab and select view code.
    past the code in the VBA editor.
    the file must be saved as a macro enabled file type such as .xlsm

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E4")) Is Nothing Or Not Intersect(Target, Range("H4")) Is Nothing Then 'test if the cell that changed is E4 or H4
        If UCase(Range("E4")) = "MOBILE SHREDDER" And UCase(Range("H4")) = "PS" Then 'Test what is in the cells
            Range("A4").ClearContents 'change the cell reference to where you want. this clears the cell
            With Range("A4").Validation 'code for data validation with list of allowable values in S2:S8. change if need
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="=$S$2:$S$8"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        Else
            Range("A4").Validation.Delete 'delete data validation if condition not met
            Range("A4").ClearContents 'cleares cell
        End If
    
    End If
    
    End Sub
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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