Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 to 2003 Formating issues | Excel Discussion (Misc queries) | |||
Formating issues | Excel Discussion (Misc queries) | |||
Mail Merge Formating Issues | Excel Discussion (Misc queries) | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
formating issues when printing using different printers | Setting up and Configuration of Excel |