VBA & Conditional Formating issues

  • Thread starter Thread starter James
  • Start date Start date
J

James

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
 
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.
 
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
 
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.
 
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))
 
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?
 
Back
Top