Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
method 'range' of object'_global' failed
I am trying to reuse the code below Code: -------------------- Public Function ColorBorder(chgcell As String) Range(chgcell).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 End With end Function -------------------- It will work the first time but if I run it again I get the following error method 'range' of object'_global' failed I read somewhere that I have to Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable. But I don't have a clue what that means are how to do it. I think the line I have to edit is Range(chgcell).Select Can someone help me out? thanks -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=563506 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
method 'range' of object'_global' failed
Hello cedtech23, If your macro will always be run on the active worksheet, change your code to this.... Code: -------------------- Public Function ColorBorder(chgcell As String) ActiveSheet.Range(chgcell).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 End With End Function -------------------- If want your code to run on any worksheet regardless if it is the active sheet, use this code... Code: -------------------- Public Function ColorBorder(wks As String, chgcell As String) Worksheets(wks).Range(chgcell).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 End With End Function -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=563506 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
method 'range' of object'_global' failed
Some possibilities:
1. chgcell is not a valid range reference (for example BXY# is invalid) 2. Since the sheet is not specified, the activesheet is assumed. If the activesheet is a chart sheet, you'll also get that error. By fully qualified, the sheet name is included in the reference Sheets("Sheet1").Range(chgcell) Normally, functions will return some type of value instead of "doing things" like changing formatting. When called from VBA, it will probably still run fine -but maybe not considered "proper". "cedtech23" wrote: I am trying to reuse the code below Code: -------------------- Public Function ColorBorder(chgcell As String) Range(chgcell).Select With Selection.Font .Name = "Arial" .FontStyle = "Bold" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 End With end Function -------------------- It will work the first time but if I run it again I get the following error method 'range' of object'_global' failed I read somewhere that I have to Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable. But I don't have a clue what that means are how to do it. I think the line I have to edit is Range(chgcell).Select Can someone help me out? thanks -- cedtech23 ------------------------------------------------------------------------ cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022 View this thread: http://www.excelforum.com/showthread...hreadid=563506 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai | Charts and Charting in Excel | |||
range method failed | Excel Programming | |||
method range failed | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Method 'Range" of object'_Global' failed | Excel Programming |