CODEWITHSUNDEEP
excelvbadrop-down-menu
user12966268
user12966268

Reputation: 3

Populate a cell, that allows manual input, with ‘£0.00’ if the word ‘absent is selected from a dropdown list

Assume I have cells A1 and B1. Cell A1 in a dropdown list. One of the words in the drop down list is ‘absent’.

When ‘absent’ is selected from cell A1, I want cell B1 to be populated with “£0,00”.

I can do this with a formula: in cell B1 such as =IF(A1="absent","£0.00","").

If any other options in the drop down are selected, the “£0.00” entry disappears (that is what I want).

Cell B1 also needs to take a manual input. For example if I select the word ‘present’ from the dropdown in A1 than a cash value needs to be manually entered in B1 e.g. £1.76.

It sort of works with the formula approach – the formula just gets over-written. Not ideal.

Is it possible using VBA to have cell B1 populated with ‘£0.00’ every time the word ‘absent’ is selected from the dropdown. It should also overwrite any value (if present) in B1 with £0.00 when the word ‘absent’ is selected. If ‘absent’ is not selected then cell B1 should still be able to accept a manual input.

Upvotes: 0

Views: 286

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

I suggest you use a Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("A1") Then
        Application.EnableEvents = False

        If LCase(Target.Value) = "absent" Then
            With Target.Offset(0, 1)
                .Value = 0
                .NumberFormat = "£0.00"
            End With
        Else
                Target.Offset(0, 1).ClearContents
        End If

    End If

    Application.EnableEvents = True
End Sub

To enter this event-triggered Macro:

  • right click on the sheet tab for the sheet containing the drop-down.
  • Select "View Code" from the right-click drop-down menu.
  • Then paste the code above into the window that opens.

EDIT

If you are dealing with multiple cells in Column A, instead of just one, here is one way to handle that:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range

'Avoid multiple selection ranges
Set Target = Target(1, 1)

'Set myRange = Range("A1:A10")

'or, to set this dynamically:

Set myRange = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

If Not Intersect(Target, myRange) Is Nothing Then
    Application.EnableEvents = False

        If LCase(Target.Value) = "absent" Then
            With Target.Offset(0, 1)
                .Value = 0
                .NumberFormat = "£0.00"
            End With
        Else
                Target.Offset(0, 1).ClearContents
        End If

End If
    Application.EnableEvents = True
End Sub

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 2006

Using Excel Formula:

You can use one more column C to avoid over writing of formula by manual entry.

If A1 is absent then the value will be £0.00 otherwise value will be picked from C column where manual entry can be made.

=IF(A1="Absent","£0.00",C1)

Using VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Cells(1, "A") = "Absent" Then
        Cells(1, "B") = "£0.00"
    Else
        Cells(1, "B") = "Enter Value"
    End If
End If
End Sub

Upvotes: 0

Related Questions