if isempty(activecell.offset(1,0).value) then
'single cell under the activecell
or maybe...
if application.counta(activecell.resize(2,1)) > 0 then
'at least one of the cells (activecell or the one below it) has something
'in it
--
Dave Peterson- Hide quoted text -
- Show quoted text -
Dave,
Thanks for your responce, however, I am having problems trying to get
it working.
What I am trying to achieve is...
Cells have pulldown menu's where the user can select 'Type 2' or 'Type
3' appointments and so on...
Type 2 will occupy two cells
Type 3 will occupy three cells etc..
If a space of only 2 cells are available say 'A3:A4', because A5 may
be occupied, then the user can not enter a Type 3 appointment, as
there is insufficient space (only 2 cells available, not 3).
So, your code .... If Application.CountA(ActiveCell.Resize(2, 1)) >
0 Then ...... should determine if sufficient space is available.
Hope this makes sense.
My code (shortened, as there are 7 Case scenarios) is as follows...
Positioned within 'This Workbook'
Private Sub Workbook_sheetChange(ByVal Sh As Object, ByVal Target As
Range)
Select Case ActiveCell
Case "Type 2"
If Application.CountA(ActiveCell.Resize(2, 1)) > 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1,
0)).Select
MsgBox ("OK to continue")
End If
Case "Type 3"
If Application.CountA(ActiveCell.Resize(3, 1)) > 0 Then
MsgBox ("Insufficient space")
Else
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(2,
0)).Select
MsgBox ("OK to continue")
End If
End Select
End Sub
Hope you can assist.
Thanks in advance.
Mik