are the addins anything to do with your code, or just examples for you to work form?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
the tm vb calendar is an example the other named "Calendar" is my code,
the what part does not work....is the sub "Private Sub initfrmCalendar()" its giving an error.
what result do you want...... well a working calendar would be nice just dont know how to get past the error
when i started the userform i got no error, i could click a date button and get the caption returned in a messagebox
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
yep cool, thats what its meant to do for now, but at my end its giving an error, for the txtday in "Private Sub initfrmCalendar()" saying wrong number of agruments, to get the error play and click the cbomonth box,
Any help in this matter?
ta
Last edited by helen85; Dec 27th, 2009 at 03:50 PM.
actually you have a large number of errors so you will just have to figure how to fix some of them, some i have found
txtday is a single textbox, not an array, so it has no index txtDay(iStartIndex).Enabled = True
this applies in several places
you need to be working with the collection of the class
you can select the month combo with no valid year, this cause a type mismatch, when trying to get iStartindex
in a couple of places you try to set the text property of a control that is a command button, use its caption property
that is all i have time for at the moment
fix some of those, then repost your updated work book, no need for the xla files
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Hi, ok the buttons do have an index property allready, heres the code, half on the form and half in a class. And also this is the collection of the class,
Code:
Set ctlCB = New cCB
ctlCB.Init txtDay, Me
colCB.Add ctlCB
Set ctlCB = New cCB
ctlCB.Init txtDay2, Me
colCB.Add ctlCB
Set ctlCB = New cCB
ctlCB.Init txtDay3, Me
colCB.Add ctlCB
Code:
Private WithEvents m_CB As MSForms.CommandButton
Private m_Form As frmCalendar
Private colCB As New Collection
Private ctlCB As cCB
Public Sub Init(ctl As CommandButton, frm As frmCalendar)
Set m_CB = ctl
Set m_Form = frm
End Sub
Private Sub m_CB_Click()
m_Form.Info m_CB
End Sub
Private Sub Class_Terminate()
Set m_CB = Nothing
Set m_Form = Nothing
End Sub
so if i do have a indexing system and the collection of the class, what else do i need to fix it?
As for the text. property instead of captions. property's there done , its a work in progress was editing until I get advice
new copy uploaded
Thanx for the help, its driving me mad and my blonde moments arent helpping, lol
all your textday controls are members of the colCB collection, but it is not indexed
you can do like
vb Code:
Private Sub initfrmCalendar()
Dim i As Integer 'Used as a counter
Dim iDay As Integer 'Used to set the day
setStartIndex 'Set the starting index, first textbox to use
setEndIndex 'Set the ending index, last textbox to use
clearTextboxes 'Clear the calendar
iDay = 1
'Loop to fill the textboxes with days
'Also to re-enable the disabled ones
Dim caption As Control
For Each caption In frmCalendar.Controls
If TypeOf caption Is CommandButton Then
caption.caption = iDay
caption.Enabled = True
caption.BackColor = vbWhite
iDay = iDay + 1
End If
Next
'' While iDay <= iEndIndex
''Dim txt
''Set txt = colCB(1).m_CB(1)
''txt.m_CB(1).caption = "Ohh"
'' colCB.Item(iStartIndex).m_CB.caption = iDay
'''Next
''' txtDay.caption = iDay
'' colCB(iStartIndex).Enabled = True 'Re-enable
'' colCB(iStartIndex).BackColor = vbWhite 'Reset the color
''
'' iDay = iDay + 1
'' iStartIndex = iStartIndex + 1
''
''
'' Wend
disableUnusedBoxes
End Sub
this still has some problems in as much as the close button gets a day value and the days are starting wrong
it would habe been better if you command buttons were named like day1 .................... day42 (or similar)
you could then use a loop like
vb Code:
for i = 1 to 42
me.controls("day" & i).caption = iday
and know you are targeting a specific control in a known order
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i would try setting iDay backwards from the first of the month to if the 1st is tuesday start iday at -1
vb Code:
for i = 1 to 42
if iday > 0 and iday < endday then
me.controls("day" & i).caption = iday
else
me.controls("day" & i).caption = ""
end if
next
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
if iday is set to start correctly for the first day of the week, then istartindex is no longer required, or use it to set the value iday iday = 2 - istartindex
the endday i used in the code sample is your setendindex, last day for that month
vb Code:
iday = 2 - istartindex
for i = 1 to 42
if iday > 0 and iday < iendindex then
me.controls("day" & i).caption = iday
else
me.controls("day" & i).caption = ""
end if
next
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Ok cool, yeap that seems to be working although its still not getting the year so it’s giving an error on setStartIndex(), as the year sets not being recognised.
Also it cant get pass the code:-
Code:
Private Sub clearButtonboxes()
Dim caption As Control
'Set each cmd's value to ""
For Each caption In frmCalendar
If TypeOf caption Is CommandButton Then caption = ""
Next
End Sub
Private Sub disableUnusedButtons()
Dim caption As Control
For Each caption In frmCalendar
If TypeOf caption Is CommandButton Then
If caption.caption = "" Then
caption.Enabled = False
caption.BackColor = &H8000000F
End If
End If
Next
End Sub
and its giving the error "Object doesn't support this property or method" for the captions
Private Sub clearButtonboxes()
Dim caption As Control
'Set each cmd's value to ""
For Each caption In frmCalendar
If TypeOf caption Is CommandButton Then caption = ""
Next
End Sub
You should refrain from using 'caption' as a variable name.
Since you are using it to reference a command button, I don't believe the default property is the 'caption' property. Try:
Code:
If TypeOf caption Is CommandButton Then caption.caption = ""
This also illustrates why you shouldn't use 'caption' as a variable name
test, if the year has not been selected in the combo, use the current year
or better set the year and month to current value in form activate
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Yeap it has the year/month has been set on form activate
Code:
Private Sub Form_Activate()
sSelectedMonth = "January" 'Default setting; need a value when the form is activated
sSelectedYear = "2010" 'Default setting; need a value when the form is activated
cboMonth.ListIndex = 0 'Go to first month
cboYear.ListIndex = 0 'Go to first year
End Sub
Its also still erroring on Me.Controls("txtday" & i).caption = "", says it cant find the contorl.
do you have a txtday1?
the original version did not, change txtday to txtday1
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
post the updated version, i will have a look again
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
setStartIndex 'Set the starting index,first textbox to use
setEndIndex 'Set the ending index, last textbox to use
clearButtonboxes 'Clear the calendar
iDay = 1
'Loop to fill the textboxes with days
'Also to re-enable the disabled ones
iDay = 1 - iStartIndex
For i = 1 To 42
Me.Controls("txtday" & i).Visible = True
Me.Controls("txtday" & i).Enabled = True
Me.Controls("txtday" & i).BackColor = vbWhite
If iDay > 0 And iDay < iEndIndex + 1 Then
Me.Controls("txtday" & i).caption = iDay
Else
Me.Controls("txtday" & i).caption = ""
Me.Controls("txtday" & i).Visible = False
End If
iDay = iDay + 1
Next
' For Each caption In frmCalendar.Controls
' If TypeOf caption Is CommandButton Then
'' caption.caption = iDay
'' if not caption
' caption.Enabled = True
' caption.BackColor = vbWhite
'' iDay = iDay + 1
' End If
'Next
'-------------------------------
'Dim i As Integer 'Used as a counter
'Dim iDay As Integer 'Used to set the day
' setStartIndex 'Set the starting index, first cmdbutton to use
' setEndIndex 'Set the ending index, last cmbutton to use
' clearButtonboxes 'Clear the calendar
' iDay = 1
'Loop to fill the cmd's with days
'Also to re-enable the disabled ones
' While iDay <= iEndIndex
' txtDay(iStartIndex).caption = iDay
' txtDay(iStartIndex).Enabled = True 'Re-enable
'txtDay(iStartIndex).BackColor = vbWhite 'Reset the color
'iDay = iDay + 1
'iStartIndex = iStartIndex + 1
'Wend
' disableUnusedButtons
End Sub
the calendar now appears to be correct, though some of the code maybe unneccessary
Last edited by westconn1; Jan 3rd, 2010 at 03:53 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i retested for every month in 2010 with correct result, if you mean that the commands with no day start and end are not shown i was under the impression (from the code) that was what you wanted, if not remove line 25, or change to enabled = false
if this does not make sense, i do not understand what is not working correctly
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
But want I would like it to do is for example if you were on the month Jan, then show the latter parts of Dec first and thefrist parts off Feb, instead of just blacking out the buttons, this was just to test that the maths was right
If iDay > iEndIndex Then iDay = 1: bacol = Me.BackColor
Next
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
i am sure i had it working correctly this morning
change to Me.Controls("txtday" & i).caption = Format(DateSerial(sSelectedYear, Month(1 & " " & sSelectedMonth & " " & sSelectedYear), iDay), "d")
i could not figure a better way to return the month number from the month string, i am sure i am overlooking something simple
this also works Me.Controls("txtday" & i).caption = Format(DateSerial(sSelectedYear, cboMonth.ListIndex + 1, iDay), "d")
you do not really need some of the variables as they are always the value of the combo boxes
i made some changes to your userform initalize to so the calendar will always start on the current month
at the top
' cboMonth.Text = "January"
' cboYear.Text = "2010"
' sSelectedYear = cboYear.Text
' sSelectedMonth = cboMonth.Text
at the end
cboYear.Text = Year(Now)
cboMonth.ListIndex = Month(Now) - 1
Last edited by westconn1; Jan 8th, 2010 at 03:17 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Hi I wonder if you can help me, as I have hit a brick wall again, one little adjustment should do it thought , the example code below (example1) is setting a date to a week number in this case “week1”, is then checking the date, etc to see if everything matches, if it does it the calls, “FindAllTHForWK2andWK6”
“FindAllTHForWK2andWK6” searches a sheet picking up values and Offsetting cells to find more information, then and here is the problem,
I am setting the correct information to the wrong button caption for example “txtDay2.caption”, when what I should be doing is checking to see if the label week2 is visible on the form and then loading the information into the right button underneath the label, using txtDay(i).caption
The main function needed are below as examples they are also right at the top of the application code
- WeekSetting
- FindAllTHForWK2andWK6
Code:
Example1:
'week1
yr = cboYear 'set the year
wkno = 43 ' starting week to find start date
fday = Weekday(DateSerial(yr, 1, 1)) - 1 ' day of first day of year (month 1 day 1)
j = (wkno - 1) * 7 + (2 - fday) 'calculate the starting day number for week no, can negative
tmp = DateSerial(yr, 1, j)
MsgBox "week " & wkno & " starts " & tmp
If cboMonth.Text = "October" And cboYear.Text = "2010" Or cboMonth.Text = "October" And cboYear.Text = "2009" Or cboMonth.Text = "October" And cboYear.Text = "2011" Or cboMonth.Text = "October" And cboYear.Text = "2012" Or cboMonth.Text = "October" And cboYear.Text = "2013" Or cboMonth.Text = "October" And cboYear.Text = "2014" Or cboMonth.Text = "October" And cboYear.Text = "2015" Or cboMonth.Text = "October" And cboYear.Text = "2016" Or cboMonth.Text = "October" And cboYear.Text = "2017" Then
LblWeek2.Left = 108
LblWeek2.Top = 210
LblWeek2.Visible = True
LblWeek6.Visible = False
FindAllTHForWK2andWK6
Example2
Private Function FindAllTHForWK2andWK6()
'Declare variables for tracking the entire range, the first found range, and the current found range.
Dim currentFind As Excel.Range
Dim sfirstFind As String
'Search For Values - WEEK2 and WEEK6 + ALL TH's
If CboTH.Text = "Theatre 2" Then
'----------TH2------------
Dim Theatre2Week2and6 As Excel.Range
Set Theatre2Week2and6 = Range("A20", "I36") 'Week set by ranges as only wk1 and wk5 have rough range values off A4-I16
' Search for the first match, specifying all the parameters except the cell to search after.
' specify all these parameters every time you call this method,
' NB: they can be overridden in the user interface.
Set currentFind = Theatre2Week2and6.Find("Theatre 2", , Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
If Not currentFind Is Nothing Then
' Keep track of the first range you find.
sfirstFind = currentFind.Address
'Continue searching as long as there are matches.
Do
MsgBox "Found Theatre 2 at: " & currentFind.Text
'Perform another search
Set currentFind = Theatre2Week2and6.FindNext(currentFind)
Loop While Not currentFind Is Nothing And currentFind.Address <> sfirstFind
End If
Dim InfoRange As Excel.Range
Dim WkDayRange As Excel.Range
' CASE: the weekday vaules
'--------- MONDAY VALUES
' Find the 1st information range for the TH to be selected
currentFind.Offset(1, 0).Select ' move 1 cell down
' Find the 2nd information range for the TH to be selected
currentFind.Offset(2, 0).Select ' move 2 cell down
' Find the 1st cell for that Weekday and Session as ref to the calendar
currentFind.Offset(1, -1).Select ' move 1 cell left and down
' Find the 2nd cell for that Weekday and Session as ref to the calendar
currentFind.Offset(2, -1).Select ' move 2 cell left and down
'Format the string
txtDay9.caption = Trim(txtDay2.caption)
' load text found to captions
txtDay9.caption = currentFind.Offset(1, -1) & "= " & currentFind.Offset(1, 0) & vbCrLf & currentFind.Offset(2, -1) & "= " & currentFind.Offset(2, 0)
'(1,0)(2,0)(1,-1)(2,-1)
'format text captions - NEEDS WORK
'ToDo: Trim
txtDay9.caption = Replace(txtDay9.caption, "MONDAY", "")
txtDay9.caption = Trim(txtDay9.caption)
it would be nice if you checked that sample workbooks actually work when you post them
i still do not understand what result you want to get on your calendar
the search string in your code (Theatre 2) is never found
your call procedures that have incorrect names or do not exist
how do you know which label you want to show?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Calendar Help
Reply With Quote
and my blonde moments arent helpping, lol 
as they are tied in with alot of the code after passing this sub
Originally Posted by helen85
