Hello!
first of all, my english is little bit rusty but I hope you'll get the point.
So I'm creating some sort of diary for daily routines, like this;
1. Person comes to work, he opens this excel-diary
2. He chooses what day it is (I'm using calendar control, is it possible that it shows current time every time it opens??)
3. He chooses Worker
4. He chooses Weather
5. He chooses temperature, first time at 7am, and another time in 12am
6. At the end of the day, he writes down working time, like 07.00am - 3.00pm
7. He writes down some notes, that came up during day, and he also writes some comments about work or something.
8. He press commandbutton "SAVE", this is linked to cell F2 where he wrote his name, file will be saved as name written on cell.
(COMMANDBUTTONS I'M USING ARE NOT ACTIVE-X COMPONENTS)
9. And after this, he press commandbutton "NEW DATE", which will open a new sheet and also copies this original sheet into it. But it only opens 30 sheets, or days.
Code I'm using for this;
Sub NewSheet()
Dim CurrentDay As Integer, NewName As String
If IsNumeric(Right(ActiveSheet.Name, 2)) Then
CurrentDay = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
CurrentDay = Right(ActiveSheet.Name, 1)
Else
Exit Sub
End If
If CurrentDay >= 30 Then
MsgBox "You cannot go higher than 30"
Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = "Day" & CurrentDay
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
Worksheets(ActiveSheet.Name).Copy After:=Worksheets(ActiveSheet.Index)
With ActiveSheet
.Name = NewName
.Range("F2:M2").ClearContents
End With
Else
Set checkWs = Nothing
MsgBox "A Worksheet named " & NewName & " already exists."
End If
End Sub
-----------------------------------------------------------------------
Problem, or the big question, is that is it possible to link Calendar control to this code, that when person has selected all those 30 days of a month, he selects new month from calendar, and is able to start everything again, from day 1 ?????? Understand??? I don't know how to explain this easily....
Bookmarks