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