VB help

  • Thread starter Thread starter ant
  • Start date Start date
A

ant

Someone gave me this code... Im new at VB and dont how
to compile this code or it not working
I just want cell D5 to be "-" as its default value until
the user enters a number

Please help

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, .Range("D5")) Is
Nothing Then _
If IsEmpty(.Value) Then _
.Value = "-"
End With
End Sub
 
Ant,

I am not sure this will do what you want. What it does is to load a - in D5
if the cell is changed to nothing.

How will the cell be initialised, and what is valid and how what happens on
each specific input?

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I just want cell D5 to be "-" as its default value until
the user enters a number
Click to expand...

Target.Range("D5") is 4 columns, 5 rows from the upper left cell of target
inclusive.
If Target is A1, Target.Range("D5") is D5
If Target is D5, Target.Range("D5") is G9

How would that benefit you?

Removing the dot in .Range("D5") will stop it from evaluating D5 as part of
Target.

Anyway, is this what you are looking for? If anything except a number is
entered in D5, it changes it to "-".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect( _
Target(1, 1), Range("D5")) Is Nothing Then
If Not (IsNumeric(Target(1, 1).Value)) Then
Target(1, 1).Value = "'-"
End If
End If
End Sub

Beyond that, consider Data Validation.
Select D5, Data-> Validation
Allow Custom. In the Formula area enter =ISNUMBER(D5)

Data validation has a critical weakness in that values can be copied/pasted
into it that bypass the validation. If that bothers you the macro might be
the way to go because it doesn't have that problem.
 
Back
Top