But you do hate top posting!

Mik wrote:

On 29 Apr, 13:23, Dave Peterson wrote:
First, you may have noticed that most responders are top posters in this forum.
(Yep, it's different here!).

You may want to start doing the same.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myStr As String
Dim HowMany As String
Dim RngToCheck As Range

Set Target = Target.Cells(1) 'just a single cell!

'just check the changes in a specific range
'I used A2:J2, change this to match what you need
If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then
Exit Sub
End If

myStr = UCase(Target.Value)

'this probably would have been a good test in the other code, too.
if mystr = "" then
'the cell has been cleared
exit sub 'or something else????
end if

Select Case myStr
Case Is = UCase("man"): HowMany = 1
Case Is = UCase("Exp"): HowMany = 2
'and so on and so on
Case Else
HowMany = 0
End Select

If HowMany = 0 Then
'something bad happened, right?
Exit Sub 'or a warning or what??
End If

Set RngToCheck = Target.Resize(HowMany, 1)

If Application.CountA(RngToCheck) 1 Then
MsgBox "Insufficient space!"
Else
MsgBox "Ok to continue" 'do you really want that?
End If

End Sub

Mik wrote:

<<Snipped





Dave,

This works great.
Thanks.

How would I perform the same task, if I were to re-label the Types?
So,
'Type 1' becomes 'Man'
'Type 2' becomes 'Exp'
'Type 3' becomes 'Fac'
etc...

Can you shed any light on this?

Thanks again.
Mik

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Fantastic.
Does exactly what i wanted.

Appreciate your help.

Mik

--

Dave Peterson