Type Mismatch...

[Darin Kramer]

Hi there,

I get a type mismatch error on the following line of code : "Set objCell
= Application.Selection" (see below)

Any idea why and how to fix ... (I swear it used to work!!!)

Thanks!!!!
D


Sub Reporting_Choice()

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim i As Byte
Dim J As Long

Set objWS = Sheets("Master")

Set objCell = Application.Selection

Select Case objCell
Case 1
Call Reporting_1
Case 2
Call Reporting_2

End Select



J = objCell.Interior.ColorIndex

' Hide columns

For i = 6 To 200

Set objR = objWS.Cells(2, i)
If objR.Interior.ColorIndex <> J Then

objWS.Columns(i).Hidden = True

End If

Next

Set objR = Nothing
Set objCell = Nothing
Set objWS = Nothing

Sheets("results").Select


End Sub

 

[Chip Pearson]

The only way I could get the error to arise was to select
something other than a range, such as a shape. One workaround is

ActiveCell.Activate
Set objCell = Application.Selection

If you're running this in Excel97 and it is attached to a command
button, set the TakeFocusOnClick property to False.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Robert Bruce]

Hi there,

I get a type mismatch error on the following line of code : "Set
objCell = Application.Selection" (see below)

Any idea why and how to fix ... (I swear it used to work!!!)

Thanks!!!!
D


Sub Reporting_Choice()

Dim objWS As Excel.Worksheet
Dim objCell As Excel.Range, objR As Excel.Range
Dim i As Byte
Dim J As Long

Set objWS = Sheets("Master")

Set objCell = Application.Selection

If your current selection is not a cell then of course this will raise an
error.

You need to ensure that a cell is selected:

If Typeof application.selection is Range then

<run your code>
else
<display error message???>

End if

HTH

Rob