Merged cells cause "Runtime error 13 type mismatch" problem

[Ralph Malph]

I am a novice excel user/programmer but have some experience with VB and VBA.
I am using Excel 2003, but I am trying to keep my spreed sheets compatible
back to 2000.

I have an excel spread sheet with the code listed below. The code will allow
some one to enter data (i.e. Student Name) into cell "M" for example. Once
that data has been entered it can not be changed without out a password. The
code keeps the rest of the spreed sheet protected unless you know the
password. This code works for columns A,E,I,M,Q,U,Y. In other words anyone
can input data into one of the cells mentioned previously if it is empty
without a password, but to erase the data or change the data as long as the
cell is not empty a password is required.

This code works great as is. However, if I take cell "M", for example, and
stretch it over cells N,O and P so as to make more room for a student name
and tell it under the "Format Cells" option to "Merge" with the other cells
my code now causes a Runtime Error '13' Type Mismatch in the following if
statement.


If Len(Target.Value) Then


I need to be able to expand the size of the cell so that it can show a full
name but I can not change the whole column width as that would cause problems
with the rest of the spreed sheet lay out.

This spreed sheet is a calendar which the students use to schedule
themselves for labs. Once they have put their name in a time slot we don't
want them to be able to change it without permission. We had a problem with a
student erasing another students name so as to steal a coveted time slot. The
code has worked great for doing this but we have found that we need more room
for the students names. The calendar spreed sheet has been laid out to look
like a regular calendar with each page showing a single month divided into
squares in a 7 by 5 square grid. The students name goes into one cell in each
of the squares representing a day. Etc.

Can anyone explain why I am getting the error when I merge them and provide
a fix.

Thank you for your help !!!

Ralph Malph



'************ Start Sample code *************

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim strPassword As String




If (Not Application.Intersect(Me.Columns("A"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("E"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("I"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("M"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("Q"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("U"), Target) Is Nothing) Or (Not
Application.Intersect(Me.Columns("Y"), Target) Is Nothing) Then

'********* The next line triggers the Runtime Error *********

If Len(Target.Value) Then

'****************************************

FormPassword.Show

'MsgBox "The Password is: " & Password, vbInformation, "What is the
Password"

strPassword = Password
Password = ""
'strPassword = InputBox("Enter password to change/remove this
name.", "Password required !")

If strPassword = "password" Then
Me.Unprotect "password"
Target.Locked = False
Exit Sub
Else
Cancel = True
MsgBox "Password Incorrect", , "Wrong password"
Exit Sub
End If
Else
Me.Unprotect "password"
Target.Locked = False
End If

End If


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Me.Unprotect "password"

Target.Locked = True

Me.Protect "password"

End Sub

'************ End Sample code *************

 

[Peter T]

If Target refers to merged cells it will refer to more than one cell, so
change

If Len(Target.Value) Then to
If Len(Target(1).Value) Then

If Len(activecell.Value) Then

In passing this is a bit shorter
If Not Intersect(Range("A:A,E:E,I:I,M:M,U:U,Y:Y"), Target) Is Nothing Then
might also want Target(1) in this line too.

Regards,
Peter T

 

[Ralph Malph]

Peter T,

THANKS !!!!

That work perfectly. I went with the following 2 code changes:

If Not Intersect(Range("A:A,E:E,I:I,M:M,Q:Q,U:U,Y:Y"), Target(1)) Is Nothing
Then

If Len(Target(1).Value) Then

It now works great. I really appreciate the quick response. Also thanks for
shortening my over sized if statement.

Ralph Malph