method 'range' of object'_global' failed

[cedtech23]

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

 

[Leith Ross]

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

 

[Guest]

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".