Hi guys,

I've got a spreadsheet that contains macros that remove columns and
re-format the contents of a report. The idea is that the users copy the
report (in xls format) and paste it in this workbook and run the macros by
clicking on a button. My macros assume the first row contains the first record

My users are not computer savvy and pasting the report in my spreadsheet by
selecting any cell other than A1 will mean that my macros will pretty much
butcher the report and the results will be useless. My solution was to unlock
only cell A1 and protect the whole spreadsheet:

Private Sub Worksheet_Activate()

With cells(1, 1)

.Locked = False
.Value = "SELECT THIS CELL AND PASTE THE REPORT RIGHT HERE"
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.Interior.Color = 65535
.Borders.LineStyle = xlContinuous
End With

Columns("A:A").ColumnWidth = 30
Rows("1:1").RowHeight = 26
activeworksheet.Protect

End Sub

This way the users will not have other option but to select cell A1. My plan
is that when they select A1 that would unprotect the worksheet so that they
would be able to paste the report. This is the code I had in mind (notice I
used the SelectionChange event; that may not be the best option but my
knowledge on programming is limited):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection = cells(1, 1) And Selection.Value = "SELECT THIS CELL AND PASTE
THE REPORT RIGHT HERE" Then
Selection.Value = ""
ActiveSheet.Unprotect
End If
End Sub

The problem is that when I paste the report that immediately triggers the
SelectionChange event and gives me an error message ("Run-time error '13':
Type mismatch"). Anybody know what could be happening here?

Thanks!

A.

The Selection is typically a Range object, but need not be. It can
also, for example, be a Shape if the user selected a shape. Your code
treats Selection both as a Range and as a text value.

If Selection = cells(1, 1) And Selection.Value = "SELECT THIS CELL AND PASTE

I assume what you really want is

If Selection.Address = "$A$1" And Selection.Value = "SELECT...." Then
' your code here

You can wrap this up with some additional validation:

If TypeOf Selection Is Excel.Range Then
If Selection.Cells.Count = 1 Then
If Selection.Value = "SELECT..." Then
' your code here
End If
End If
End If

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Thu, 4 Feb 2010 14:00:18 -0800, Alejandro
wrote:

Hi guys,

I've got a spreadsheet that contains macros that remove columns and
re-format the contents of a report. The idea is that the users copy the
report (in xls format) and paste it in this workbook and run the macros by
clicking on a button. My macros assume the first row contains the first record

My users are not computer savvy and pasting the report in my spreadsheet by
selecting any cell other than A1 will mean that my macros will pretty much
butcher the report and the results will be useless. My solution was to unlock
only cell A1 and protect the whole spreadsheet:

Private Sub Worksheet_Activate()

With cells(1, 1)

.Locked = False
.Value = "SELECT THIS CELL AND PASTE THE REPORT RIGHT HERE"
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.Interior.Color = 65535
.Borders.LineStyle = xlContinuous
End With

Columns("A:A").ColumnWidth = 30
Rows("1:1").RowHeight = 26
activeworksheet.Protect

End Sub

This way the users will not have other option but to select cell A1. My plan
is that when they select A1 that would unprotect the worksheet so that they
would be able to paste the report. This is the code I had in mind (notice I
used the SelectionChange event; that may not be the best option but my
knowledge on programming is limited):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection = cells(1, 1) And Selection.Value = "SELECT THIS CELL AND PASTE
THE REPORT RIGHT HERE" Then
Selection.Value = ""
ActiveSheet.Unprotect
End If
End Sub

The problem is that when I paste the report that immediately triggers the
SelectionChange event and gives me an error message ("Run-time error '13':
Type mismatch"). Anybody know what could be happening here?

Thanks!

A.