Assign a worksheet name to a variable

[benjammind]

Hi,

Advice / help /code would be greatly appreciated.

I need to figure out how to assign the name of a worksheet to
variable. My situation is:
I have an combo box containing a list of year's, when a year i
selected a new worksheet is created and renamed to the year selected.
userform containing inputboxes is then used to enter dates onto the ne
sheet.

I need to figure out a way of ensuring only certain dates are entere
into certain sheets. (eg Only dates in 2005 can be entered into th
2005 sheet, etc etc)

The only way I have thought of doing this, (before the code fires tha
enters the dates onto the sheet) is to compare whatever date the use
wants to enter (in the form of the inputbox) against the name of th
spreadheet, I dont know how to do this, I assume that the workshee
name has be assigned to a variable before this can happen?

I would really appreciate any input /help.
Thanks
Be

 

[STEVE BELL]

This works if everything is a date (place it in the worksheet module)
Errors out if cell is text, or sheet name not equal to a year (text or
numeric)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer, y As Double

x = Year(Target)
y = CDbl(ActiveSheet.Name)
If x = y Then
MsgBox "OK"
Else
MsgBox "Wrong Year"
Application.Undo

End If
End If
End Sub

 

[Bob Phillips]

Is the sheet just created the activesheet now? If so, you could simply use
code like##


If Year(CLng(TextBox1.Text)) = activesheet.name then
'OK
Else
'Not OK
End If

 

[benjammind]

Hello Bob,
Yes it is the activesheet, many thanks for your reponse. Another point
of view is most helpful!

Ben