Getting error using Month()

[HammerJoe]

Hi,

I am confused and need some assistance.
In my Vba code I am trying to verify if todays month is different than
the one in cell.
Problem is I am getting an error using the Month().

MsgBox (Month(MainWorksSheet.Range("B19").Value))

The code above gives me the error that the Object doesnt support the
property.
The range value is a date 2008/07/02.

Using the Year() function it works and returns 2008 as it should.
What am I missing with the month function?

Thanks

 

[Mike H]

Try this

MsgBox Month(Sheets("mainworksheet").Range("B19").Value)

Mike

 

[HammerJoe]

Hi,

Mainworksheet is a Worksheet variable that was defined the sheet name
that I need to work on.
I dont think that is the problem.
The problem is with the month() function that gives an error.
I can get the date result from MainWorksSheet.Range("B19").Value, but
month() complains about it.
Cell B19 is defined as date and it contains todays date on the format
of "2008/02/08"
Why can't month() work with it??

 

[HammerJoe]

Can someone help me with this?

I dont know why the month() doesnt work and I need to compare todays
date with a date on a cell and match the month.
If the month are the same then do something, if it is different then
do something else.

Thanks

 

[Ron Rosenfeld]

Mainworksheet is a Worksheet variable that was defined the sheet name
that I need to work on.
I dont think that is the problem.
The problem is with the month() function that gives an error.
I can get the date result from MainWorksSheet.Range("B19").Value, but
month() complains about it.
Cell B19 is defined as date and it contains todays date on the format
of "2008/02/08"
Why can't month() work with it??

I canNOT reproduce your problem with this:

=================================
Option Explicit
Sub foo()
Dim MainWorksSheet As Worksheet
Set MainWorksSheet = ActiveSheet
MsgBox (Month(MainWorksSheet.Range("B19").Value))
End Sub
====================================

Let us see some more of your code.
--ron

 

[HammerJoe]

Here is what I have in module1:

A few notes about it.
I noticed that the Public MainWorkSheet is missing a "s" and it is not
defined inside the open sub.
Notices the "s" on the line Set MainWorksSheet .
If I try to fix this, this causes me another problem and still doesnt
solve my month() problem.

If i declare a public MainWorksSheet or a DIM MainWorksSheet as
Worksheet inside the sub then
MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"
stops working????
(These are two buttons created in the sheet for interaction using the
toolbox)
Untli someones explain this to me I dont know what is going on.

Thanks for the help.

=================================
Option Explicit
Public MainWorkSheet as worksheet

sub open ()

Dim StartWeekRange, EndWeekRange As Range
Dim TempWeekday As Integer
Dim WorkingPath As String
Set MainWorksSheet = ThisWorkbook.Worksheets("Main")

Set StartWeekRange = MainWorksSheet.Range("H2")
Set EndWeekRange = MainWorksSheet.Range("J2")

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"


MainWorksSheet.TextBoxAccount.Value = ""
MainWorksSheet.TextBoxNotes.Value = ""

TodayWeekDay = ""
TempWeekday = Weekday(Date)
'if new month call UpdateWeeklyValues("New Month")

'new week

'MsgBox Month(Sheets("Main").Range("b19").Value)

StartWeekRange.Value = (Date - (TempWeekday - 2))
EndWeekRange.Value = (Date + (7 - TempWeekday))

'Dim TempMonth
'If Date < DateSerial(Year(Date), Month(Date) + 1, 0) Then
'MsgBox (Month(MainWorksSheet.Range("B19").Value))
'End If
'TempMonth = Month(MainWorksSheet.Range("B19").Value)
'TempMonth = (DateSerial(Year(Date),
Month(MainWorksSheet.Range("B19").Value), 1)) '
Month(MainWorksSheet.Range("B19").Value)

'If Month(MainWorksSheet.Range("B19").Value) <
(DateSerial(Year(Date), Month(Date), 1)) Then
'UpdateWeeklyValues ("New Month")
'End If


If MainWorksSheet.Range("B19").Value <> Date Then
'add another work day to the monthly sheet
ThisWorkbook.Worksheets("MonthlyTrack").Range("L8").Value =
ThisWorkbook.Worksheets("MonthlyTrack").Range("L8").Value + 1

MainWorksSheet.LabelMakeBusy.Caption = "00:00:00"
MainWorksSheet.Range("B19").Value = Date
End If

MainWorksSheet.Activate
MainWorksSheet.Range("A1").Select

end sub

 

[Ron Rosenfeld]

Here is what I have in module1:

A few notes about it.
I noticed that the Public MainWorkSheet is missing a "s" and it is not
defined inside the open sub.
Notices the "s" on the line Set MainWorksSheet .
If I try to fix this, this causes me another problem and still doesnt
solve my month() problem.

If i declare a public MainWorksSheet or a DIM MainWorksSheet as
Worksheet inside the sub then
MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"
stops working????
(These are two buttons created in the sheet for interaction using the
toolbox)
Untli someones explain this to me I dont know what is going on.

Thanks for the help.

=================================
Option Explicit
Public MainWorkSheet as worksheet

sub open ()

Dim StartWeekRange, EndWeekRange As Range
Dim TempWeekday As Integer
Dim WorkingPath As String
Set MainWorksSheet = ThisWorkbook.Worksheets("Main")

Set StartWeekRange = MainWorksSheet.Range("H2")
Set EndWeekRange = MainWorksSheet.Range("J2")

MainWorksSheet.CommandButtonAdd.Enabled = True
MainWorksSheet.CommandButtonMakeBusy.Caption = "Start"


MainWorksSheet.TextBoxAccount.Value = ""
MainWorksSheet.TextBoxNotes.Value = ""

TodayWeekDay = ""
TempWeekday = Weekday(Date)
'if new month call UpdateWeeklyValues("New Month")

'new week

'MsgBox Month(Sheets("Main").Range("b19").Value)

StartWeekRange.Value = (Date - (TempWeekday - 2))
EndWeekRange.Value = (Date + (7 - TempWeekday))

'Dim TempMonth
'If Date < DateSerial(Year(Date), Month(Date) + 1, 0) Then
'MsgBox (Month(MainWorksSheet.Range("B19").Value))
'End If
'TempMonth = Month(MainWorksSheet.Range("B19").Value)
'TempMonth = (DateSerial(Year(Date),
Month(MainWorksSheet.Range("B19").Value), 1)) '
Month(MainWorksSheet.Range("B19").Value)

'If Month(MainWorksSheet.Range("B19").Value) <
(DateSerial(Year(Date), Month(Date), 1)) Then
'UpdateWeeklyValues ("New Month")
'End If


If MainWorksSheet.Range("B19").Value <> Date Then
'add another work day to the monthly sheet
ThisWorkbook.Worksheets("MonthlyTrack").Range("L8").Value =
ThisWorkbook.Worksheets("MonthlyTrack").Range("L8").Value + 1

MainWorksSheet.LabelMakeBusy.Caption = "00:00:00"
MainWorksSheet.Range("B19").Value = Date
End If

MainWorksSheet.Activate
MainWorksSheet.Range("A1").Select

end sub

I guess I'm not sure what you are presenting.

Your Sub does not work at all on my system. Open is a keyword so there's a
naming problem right at the start.

Certainly, with a date in B19, and MainWorksSheet set to ActiveSheet, the
following works properly:

Month(MainWorksSheet.Range("B19").Value)

I wish I could help you more but perhaps if you just start with a small part of
your Sub, get it working, and then add on the other required segments, you will
have better luck.


--ron

 

[HammerJoe]

Sorry,

I didnt copy that line and it was typed.

It is Sub Auto_open() on module1.

I cant get month() to work and on top of that if I dim MainWorksSheet
as Worksheet then I get an error on the two buttons I have set up on
that sheet
What is going on??

 

[HammerJoe]

I can send the file via email to anyone wanting to take a crack at it?