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
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

--
benjammin
-----------------------------------------------------------------------
benjammind's Profile: http://www.excelforum.com/member.php...fo&userid=1821
View this thread: http://www.excelforum.com/showthread.php?threadid=38487

Assign a worksheet name to a variable
 

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" wrote
in message ...

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

Assign a worksheet name to a variable
 

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" wrote
in message ...

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

Assign a worksheet name to a variable
 

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

Ben


--
benjammind
------------------------------------------------------------------------
benjammind's Profile: http://www.excelforum.com/member.php...o&userid=18217
View this thread: http://www.excelforum.com/showthread...hreadid=384877