PDA

View Full Version : Selection = <Type mismatch>



danitouffaha
08-22-2014, 07:40 AM
Hi,

I have a vbs script that is scheduled to run daily, opens an excel workbook, unprotects it, does some tasks then exits after protecting it again.
The process worked for a good time flawlessly.
Recently, I started getting an error when exiting.
Code:
when the script opens the workbook, there is no problem

Private Sub Workbook_Open()
Call logStartFunctionSub(True, True, "Workbook_Open", Me.Name)
Set selRange = Selection ' this line works perfectly
activeRow = ActiveCell.Row
activeColumnNb = ActiveCell.Column
activeSheetName = ActiveSheet.Name
............
end Sub
but when exiting :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call logStartFunctionSub(True, True, "Workbook_BeforeClose", Me.Name)
Dim activeSheetName As String
'On Error Resume Next
Set selRange = Selection ' here if I hover Selection I see "Selection = <Type mismatch>" and this is throwing an error. Adding the On Error is not an option, because this line is used all over the code, and this will leave the workbook unprotected. PLEASE HELP as this is driving me crazy
activeRow = ActiveCell.Row
activeColumnNb = ActiveCell.Column
activeSheetName = ActiveSheet.Name
......
End Sub

I tried to add an explicit range set before the problematic line and it is always erroring out.
Any ideas?

Thanks a lot
Dani

Bob Phillips
08-22-2014, 07:56 AM
Why not be explicit with the sheet and range, using selection is flaky practice.

Paul_Hossler
08-22-2014, 08:05 AM
Assuming that selRange is Dim-ed as a Range, I'm guessing that whatever is selected (the 'Selection') is not a Range (e.g. a Textbox or something)


Maybe make sure that the correct / intended range is selected or use to be sure



If Typeof Selection is Range then Set selRange = Selection

danitouffaha
08-22-2014, 12:35 PM
Why not be explicit with the sheet and range, using selection is flaky practice.

I get the initial selection at the beginning of the sub call because when I am done at the end of the Sub I have to reselect the initial selection.

I tried the following

set Selection = Sheets("Admin").range("A1")

I got the same error

thanks

danitouffaha
08-22-2014, 12:38 PM
thanks for your reply,

selRange is dim-ed as range, the selection (I checked) is a cell on the sheet
I cannot use your code snippet because the selection itself at this time will error out since when I hover over the "Selection" I am getting type mismatch

Any ideas?

thanks

Bob Phillips
08-22-2014, 04:39 PM
I get the initial selection at the beginning of the sub call because when I am done at the end of the Sub I have to reselect the initial selection.

I tried the following

set Selection = Sheets("Admin").range("A1")

I got the same error

thanks

Not

Set Selection = ...

but

Set selRange = ...

danitouffaha
08-23-2014, 09:36 AM
Not

Set Selection = ...

but

Set selRange = ...

If I use the second, it defeats the purpose for which Selection, I want the initial selected range to be reselected at the end of the sub call, but using the second I will be forcing a new range.

The only way I see it now to solve this, is the following, it is stupid that I have to do so but it kind of resolves my issue for now
if isAuto = true then 'is the variable that I use to know if the VBS script is running
set selRange = sheets("Admin").range("A1")
else
set selRange=Selection
endif

Thanks

Paul_Hossler
08-23-2014, 10:25 AM
In a Standard Module


Public rSaveTheRange as Range


In WB Open


Set rSaveTheRange=Selection


In WB BeforeSave (BTW, to select a range you don't use 'Set Selecttion = ....' )


rSaveTheRange.Select


This is very not-foolproof

mikerickson
08-24-2014, 07:52 AM
Application.Goto rSaveTheRange is more foolproof than using the .Select method.

Paul_Hossler
08-24-2014, 07:00 PM
is more foolproof than using the .Select method.


Didn't know that. How so?

mikerickson
09-02-2014, 10:41 PM
With myRange.Select, the worksheet myRange.Parent has to be active or there will be an error.

Application.Goto myRange will go to myRange no matter what sheet or workbook is active.

snb
09-03-2014, 02:28 AM
If you show us the code for


logStartFunctionSub(True, True, "Workbook_Open", Me.Name)


We will show you any 'select', Activate' or application.goto instruction is unnecessary.

Paul_Hossler
09-03-2014, 06:01 AM
@Mike --



With myRange.Select, the worksheet myRange.Parent has to be active or there will be an error.
Application.Goto myRange will go to myRange no matter what sheet or workbook is active.


Handy tip

Thanks