Range("Weekending").Cells.Columns.Count

[Carl Brehm]

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

 

[Tom Ogilvy]

If you are using xl97, set the takefocus on click property of the command
button to false.

--
Regards,
Tom Ogilvy

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

 

[Carl Brehm]

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]

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.

 

[Carl Brehm]

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