Thanks for your help.
I will just move it to a normal module and keep code on a sheet to only what
has to be there.

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores
"Tom Ogilvy" wrote in message
...
I said if you are using xl97 to change TakeFocusOnClick propery to false.
If you were using xl97 it would make a lot of sense. You didn't say, so I
took a guess.

Your problem is probably that:
Weekending is a rang on "sheet2"
and this code is not in the sheet2 code module. In that case, unlike in a
"normal basic" module you must qualify Range("WeekEnding") like so

worksheets("Sheet2").Range("WeekEnding")

Sorry for the bad guess. It is a popular source of your error message.

--
Regards,
Tom Ogilvy


"Carl Brehm" wrote in message
...
Using 2000 V9.0.2720
set the takefocus = false
Makes no sense as Expand_Named_Range "Weekending", "right", 1
works fine.
Weekending is a rang on "sheet2"
Function Expand_Named_Range is in a normal basic module,
not in sheet1 code

Carl Brehm
Lake Lafourche Bird House
Hebert, LA

"Tom Ogilvy" wrote in message
...
If you are using xl97, set the takefocus on click property of the
command
button to false.

--
Regards,
Tom Ogilvy

"Carl Brehm" wrote in message
...
Why would
columncount = Range("Weekending").Cells.Columns.Count
return error 1004 (Method rage of object worksheet failed)?

Code is located on sheet1 activated by button
=EMBED("Forms.CommandButton.1","")


Private Sub Start_new_week_Click()
Dim monthdate As String
Dim columncount As Integer

monthdate = Application.InputBox("Week Ending Date", "Date", , , , , ,
2)
'gets date of current week ends
Sheets("sheet1").Range("i4").Value = monthdate

Expand_Named_Range "Weekending", "right", 1
'expands named range one column

columncount = Range("Weekending").Cells.Columns.Count
'Counts number of colums in named range

With Range("weekending")
.Offset(0, columncount - 1).Value = monthdate
'assigns weekending date to column heading
'on Worksheet("vendor_totals")
End With


--
Carl Brehm
Lake Lafourche Bird House
Hebert, LA

Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Lovebirds, Cherry Head Conures
Prince of Whales

Wholesale Cages to Breeders & Pet Stores