Hello Guys, im currently building a scheduling system. In a nutshell, what im trying to do, at this point, is pop an input box asking for a specific date to search for then if that is found, activate the sheet that the date was found on. The date will always be in H1 of a sheet. I found the following code online and attempted to tweak it to my needs. It pops the inputbox and allows the user to enter the date but never finds it. the format in H1 is "November 10, 2016". It gets that date from a formula:
(The sheet names will probably change and be the actual date)
Some other questions here would be. (1)The way it looks in this code, the input needs to be exact. Is there a way to tell it to just look for the date entered no matter what format it is in?
also, if that date isn't found, id like to pop another box asking if the user would like to add a sheet with that date as the name.
Private Sub cmdViewCurrent_Click()
'// Find cell on sheet H1 that contains a specific date
Dim cn As String, cn2 As String, ws As Worksheet
cn = InputBox("Enter the Date You Are Looking For As Month Name, Day, Year")
retry:
If cn = "" Then Exit Sub
cn = Trim(LCase(cn))
Dim cn3 As String
cn3 = cn
For Each ws In ActiveWorkbook.Sheets
cn2 = Trim(LCase(Sheets("Daily Appointment Calendar").Range("H1").Value))
cn2 = Replace(cn2, Chr(160), "")
If cn2 = cn Then 'HELP, Working with Active Cell
Worksheets("Daily Appointment Calendar").Activate 'must do this first
Worksheets("Daily Appointment Calendar").Range("H1").Activate
Exit Sub
End If
'Next ws
cn = InputBox(cn & " -- Not Found" & Chr(10) & _
"Enter a Different date, or Click [Cancel]")
GoTo retry
Next ws
End Sub
Display More
Any help / ideas are all greatly appreciated.
EDIT: is there also a way to create multiple sheets at the same time giving a name to each sheet of the current date for the first sheet then the next day for the next sheet and so on fo the whole month? The workbook will then be saved as the month name but I haven't got that far yet! Thanks again!