Assign a worksheet name to a variable

Hi,

Advice / help /code would be greatly appreciated.

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

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

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

I would really appreciate any input /help.
Thanks
Ben

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



--
steveB

Remove "AYN" from email to respond
"benjammind" <benjammind.1rr73w_1120662709.4713@excelforum-nospam.com> wrote
in message news:benjammind.1rr73w_1120662709.4713@excelforum-nospam.com...
>
> Hi,
>
> Advice / help /code would be greatly appreciated.
>
> I need to figure out how to assign the name of a worksheet to a
> variable. My situation is:
> I have an combo box containing a list of year's, when a year is
> selected a new worksheet is created and renamed to the year selected. A
> userform containing inputboxes is then used to enter dates onto the new
> sheet.
>
> I need to figure out a way of ensuring only certain dates are entered
> into certain sheets. (eg Only dates in 2005 can be entered into the
> 2005 sheet, etc etc)
>
> The only way I have thought of doing this, (before the code fires that
> enters the dates onto the sheet) is to compare whatever date the user
> wants to enter (in the form of the inputbox) against the name of the
> spreadheet, I dont know how to do this, I assume that the worksheet
> name has be assigned to a variable before this can happen?
>
> I would really appreciate any input /help.
> Thanks
> Ben
>
>
> --
> benjammind
> ------------------------------------------------------------------------
> benjammind's Profile:
> http://www.excelforum.com/member.php...o&userid=18217
> View this thread: http://www.excelforum.com/showthread...hreadid=384877
>

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

--
HTH

Bob Phillips

"benjammind" <benjammind.1rr73w_1120662709.4713@excelforum-nospam.com> wrote
in message news:benjammind.1rr73w_1120662709.4713@excelforum-nospam.com...
>
> Hi,
>
> Advice / help /code would be greatly appreciated.
>
> I need to figure out how to assign the name of a worksheet to a
> variable. My situation is:
> I have an combo box containing a list of year's, when a year is
> selected a new worksheet is created and renamed to the year selected. A
> userform containing inputboxes is then used to enter dates onto the new
> sheet.
>
> I need to figure out a way of ensuring only certain dates are entered
> into certain sheets. (eg Only dates in 2005 can be entered into the
> 2005 sheet, etc etc)
>
> The only way I have thought of doing this, (before the code fires that
> enters the dates onto the sheet) is to compare whatever date the user
> wants to enter (in the form of the inputbox) against the name of the
> spreadheet, I dont know how to do this, I assume that the worksheet
> name has be assigned to a variable before this can happen?
>
> I would really appreciate any input /help.
> Thanks
> Ben
>
>
> --
> benjammind
> ------------------------------------------------------------------------
> benjammind's Profile:
http://www.excelforum.com/member.php...o&userid=18217
> View this thread: http://www.excelforum.com/showthread...hreadid=384877
>

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

Ben