VBA & Conditional Formating issues
 

Hi Guys

I know little bit of VBA & use XL2002. I am creating a
macro which works fine when called from any workbook with
the exception of a workbook which may have any Conditional
Formating.

Here is the code


On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column" &
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


If the worksheet has a condtional formation the following
condition becomes true regardless of the fact that a Range
of data was selected
If MyRange.Cells.Count = 0 Then Exit Sub

Why it does that, is there a way around ..a fix (i do need
to keep Conditional formating on the sheet as well)

Thanks a lot for ur help in advance

-James

VBA & Conditional Formating issues
 

remove On Error Resume Next ( turn on Break on All Errors)

and see what error you get.

then only reason you would have count = 0 is if the interaction with the
inputbox caused an error and myRange was not set.

You should have this type of checking anyway

On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

On Error goto 0
if myRange is nothing then
msgbox "No selection made, myrange is nothing"
Exit Sub
End if

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column" &
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


but you shouldn't just turn on
On Error Resume Next - it will mask all your problems and you won't know
what is happening. Use it only when you need it.

--
Regards,
Tom Ogilvy


"James" wrote in message
...
Hi Guys

I know little bit of VBA & use XL2002. I am creating a
macro which works fine when called from any workbook with
the exception of a workbook which may have any Conditional
Formating.

Here is the code


On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column" &
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


If the worksheet has a condtional formation the following
condition becomes true regardless of the fact that a Range
of data was selected
If MyRange.Cells.Count = 0 Then Exit Sub

Why it does that, is there a way around ..a fix (i do need
to keep Conditional formating on the sheet as well)

Thanks a lot for ur help in advance

-James

VBA & Conditional Formating issues
 

Hi Tom
Thanks for quick response
I tried with the sugessted changes & got the message "my
rane is nothing", trien removing On error resume next got
error on inputbox saying Run Time Error 424, Obeject
required. Interestingly it crashes only on a sheet with
conditional formating but never on regular sheets.
Any thoughts..or may be its just Excel bug

Thanks
-----Original Message-----
remove On Error Resume Next ( turn on Break on All
Errors)

and see what error you get.

then only reason you would have count = 0 is if the
interaction with the
inputbox caused an error and myRange was not set.

You should have this type of checking anyway

On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

On Error goto 0
if myRange is nothing then
msgbox "No selection made, myrange is nothing"
Exit Sub
End if

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column" &
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


but you shouldn't just turn on
On Error Resume Next - it will mask all your problems
and you won't know
what is happening. Use it only when you need it.

--
Regards,
Tom Ogilvy


"James" wrote in
message
...
Hi Guys

I know little bit of VBA & use XL2002. I am creating a
macro which works fine when called from any workbook
with
the exception of a workbook which may have any
Conditional
Formating.

Here is the code


On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column"
&
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


If the worksheet has a condtional formation the
following
condition becomes true regardless of the fact that a
Range
of data was selected
If MyRange.Cells.Count = 0 Then Exit Sub

Why it does that, is there a way around ..a fix (i do
need
to keep Conditional formating on the sheet as well)

Thanks a lot for ur help in advance

-James


.

VBA & Conditional Formating issues
 

I am not aware of any adverse interaction that would be cause with the code
you show and a sheet with conditional formatting. Is that sheet protected?
perhaps enable selection is turned off for that sheet as well.

--
Regards,
Tom Ogilvy

James wrote in message
...
Hi Tom
Thanks for quick response
I tried with the sugessted changes & got the message "my
rane is nothing", trien removing On error resume next got
error on inputbox saying Run Time Error 424, Obeject
required. Interestingly it crashes only on a sheet with
conditional formating but never on regular sheets.
Any thoughts..or may be its just Excel bug

Thanks
-----Original Message-----
remove On Error Resume Next ( turn on Break on All
Errors)

and see what error you get.

then only reason you would have count = 0 is if the
interaction with the
inputbox caused an error and myRange was not set.

You should have this type of checking anyway

On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

On Error goto 0
if myRange is nothing then
msgbox "No selection made, myrange is nothing"
Exit Sub
End if

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column" &
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


but you shouldn't just turn on
On Error Resume Next - it will mask all your problems
and you won't know
what is happening. Use it only when you need it.

--
Regards,
Tom Ogilvy


"James" wrote in
message
...
Hi Guys

I know little bit of VBA & use XL2002. I am creating a
macro which works fine when called from any workbook
with
the exception of a workbook which may have any
Conditional
Formating.

Here is the code


On Error Resume Next
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

If MyRange.Cells.Count = 0 Then Exit Sub
If MyRange.Columns.Count < 1 Then
MsgBox "You Can Only Select Data in One Column"
&
vbCrLf & "Please Try Agian", vbOKOnly +
vbInformation, "Boo Boo..."
Exit Sub
End If


If the worksheet has a condtional formation the
following
condition becomes true regardless of the fact that a
Range
of data was selected
If MyRange.Cells.Count = 0 Then Exit Sub

Why it does that, is there a way around ..a fix (i do
need
to keep Conditional formating on the sheet as well)

Thanks a lot for ur help in advance

-James


.

VBA & Conditional Formating issues
 

Not familiar with the Excel 2002 InputBox, but AFAIK inputbox only
returns a string.

Your prompt says "please select". If the inputbox allows a range
selection to be made you could presumably use :-
Dim MyRange as Range
Set MyRange=Selection

Otherwise you need to use

Set MyRange = Range(Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8))


---
Message posted from http://www.ExcelForum.com/

VBA & Conditional Formating issues
 

OP Posted:
Set MyRange = Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8)

This works (and the OP stated it works on almost every sheet).

BrianB posted:

Set MyRange = Range(Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8))

Which raises an error.

???

Also, selecting with application.Inputbox does not change the selection -
not sure what you intended with your first suggestion? Don't prompt, the
user must select the range before running the macro?

--
Regards,
Tom Ogilvy


"BrianB " wrote in message
...
Not familiar with the Excel 2002 InputBox, but AFAIK inputbox only
returns a string.

Your prompt says "please select". If the inputbox allows a range
selection to be made you could presumably use :-
Dim MyRange as Range
Set MyRange=Selection

Otherwise you need to use

Set MyRange = Range(Application.InputBox _
(Prompt:="Please Select a Data Range
(Block) to Search for", Title:=".", Type:=8))


---
Message posted from http://www.ExcelForum.com/