Trouble with changing cell numberformat with VBA

[jmwbowen]

Hi,

I believe Range.Numbeformat is the property I am looking for.

I have a cell with a dropdown picker. If the user chooses "Distractor" from the picker, Worksheet_Change triggers an Inputbox to open, asking what kind of distractor it is. We'll use "Gloves" for this example.

I want the cell to display "Distractor - Gloves" while keeping the actual value as "Distractor"

The following is not working for me:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DistractorText As String
Select Case Target.Address
Case "$B$23"
    If Target.Value = "Distractor" Then
        DistractorText = InputBox("Type of Distractor:")
        Target.NumberFormat = "@" & " - " & DistractorText

FYI: There's more code, but I copy-pasted the trouble area. The last line using the NumberFormat property is giving me trouble.

Help?

 

[RoryA]

Try:

Target.NumberFormat = "@"" - " & DistractorText & """"

 

[jmwbowen]

Thank you moderator, that did it!

On a separate note, is there a way to mark a thread as "solved"? I'm new here.

 

[RoryA]

No, we don't use that. A simple 'thank you' at the end is enough of an indicator.

Welcome aboard, but the way.