Basic question
 

I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but basically
I'd like cells to automatically fill in other cells based on values the users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

That is an on demand macro that the user would have to run on their own. You
could use the On Change event from Sheet 2 if you needed it to be automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own. You
could use the On Change event from Sheet 2 if you needed it to be automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

This should be close to what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Address = "$C$12" Then
If Target.Value = "N" Then
Worksheet("Sheet3").Cells(3, 2) = "Sold Out"
ElseIf Target.Value = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

This code needs to be included in the sheet and not in a standard module.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own. You
could use the On Change event from Sheet 2 if you needed it to be automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

did you run a macro like

Sub StartOnChange()
Worksheets("Sheet2").OnChange = "MyMacro"
End Sub

--
Regards,
Tom Ogilvy

"hshayh0rn" wrote in message
...
From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it
to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own.
You
could use the On Change event from Sheet 2 if you needed it to be
automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro
manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer
on here
(probably to basic). I have a spreadsheet that I would like to
perform all
of
my calculations / If / Then statements using VBA but I'm not sure
how to
actually get the code to run or where to put it to run... I can
get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on
values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if
someone could
shoot me a quick answer I would appreciate it.

Basic question
 

Sorry, this should have been OnEntry rather than OnChange.

Sub StartOnChange()
Worksheets("Sheet2").OnEntry = "MyMacro"
End Sub

Or perhaps you were talking about the Change Event, a sheet level event.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
did you run a macro like

Sub StartOnChange()
Worksheets("Sheet2").OnChange = "MyMacro"
End Sub

--
Regards,
Tom Ogilvy

"hshayh0rn" wrote in message
...
From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add
it
to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their
own.
You
could use the On Change event from Sheet 2 if you needed it to be
automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro
manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code
module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer
on here
(probably to basic). I have a spreadsheet that I would like to
perform all
of
my calculations / If / Then statements using VBA but I'm not
sure
how to
actually get the code to run or where to put it to run... I can
get all of
the code to run with an on click event or something like that
but
basically
I'd like cells to automatically fill in other cells based on
values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if
someone could
shoot me a quick answer I would appreciate it.

Basic question
 

I still seem to be having an issue with the code. I r-clicked on the
worksheet I need to place the code in. I changed the drop down from "general"
to "worksheet" and then selected "change". I then pasted the code into the
window and went back and made the change that should have triggered the
action on the spreadsheet. I then get a compile error that says:

"sub or function not defined"

I tried the code you gave me using Worksheet("sheet7") and Worksheet("name
of worksheet") and neither seems to work. When I look at the code in error it
seems to be having an issue with the word worksheet. FYI - I'm running
Excel2003

"Jim Thomlinson" wrote:

This should be close to what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Address = "$C$12" Then
If Target.Value = "N" Then
Worksheet("Sheet3").Cells(3, 2) = "Sold Out"
ElseIf Target.Value = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

This code needs to be included in the sheet and not in a standard module.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own. You
could use the On Change event from Sheet 2 if you needed it to be automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

Sorry I copied some of Bob's code without looking to close. It shoud say
worksheets not worksheet... We should mark this on the calendar because it is
rare that Bob makes a mistake... :-)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Address = "$C$12" Then
If Target.Value = "N" Then
Worksheets("Sheet3").Cells(3, 2) = "Sold Out"
ElseIf Target.Value = "Y" Then
Worksheets("Sheet3").Visible = False
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

I still seem to be having an issue with the code. I r-clicked on the
worksheet I need to place the code in. I changed the drop down from "general"
to "worksheet" and then selected "change". I then pasted the code into the
window and went back and made the change that should have triggered the
action on the spreadsheet. I then get a compile error that says:

"sub or function not defined"

I tried the code you gave me using Worksheet("sheet7") and Worksheet("name
of worksheet") and neither seems to work. When I look at the code in error it
seems to be having an issue with the word worksheet. FYI - I'm running
Excel2003

"Jim Thomlinson" wrote:

This should be close to what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Address = "$C$12" Then
If Target.Value = "N" Then
Worksheet("Sheet3").Cells(3, 2) = "Sold Out"
ElseIf Target.Value = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

This code needs to be included in the sheet and not in a standard module.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own. You
could use the On Change event from Sheet 2 if you needed it to be automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer on here
(probably to basic). I have a spreadsheet that I would like to perform all
of
my calculations / If / Then statements using VBA but I'm not sure how to
actually get the code to run or where to put it to run... I can get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if someone could
shoot me a quick answer I would appreciate it.

Basic question
 

Sorry, I thought from your original posting that you had tried the event
route and wanted a different route. I think Jim has got you sorted now.

Regards

Bob

"hshayh0rn" wrote in message
...
From the last time I posted I found the on change event and thought that
might be the answer but the code Bob supplied does not work when I add it
to
the on change event.

"Jim Thomlinson" wrote:

That is an on demand macro that the user would have to run on their own.
You
could use the On Change event from Sheet 2 if you needed it to be
automatic.
--
HTH...

Jim Thomlinson


"hshayh0rn" wrote:

Thanks for the reply... So, would the user have to run the macro
manually or
does excel know to run this on its own?

"Bob Phillips" wrote:

Sub myMacro()
If Worksheet("Sheet2").cells(12,3) = "N" Then
Worksheet("Sheet3").cells(3,2) = "Sold Out"
ElseIf Worksheet("Sheet2").cells(12,3) = "Y" Then
Worksheet("Sheet3").Visible = False
End If
End Sub

This would be an on-demand macro, stored in a standard code module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hshayh0rn" wrote in message
...
I have a very basic question but I can't seem to find the answer
on here
(probably to basic). I have a spreadsheet that I would like to
perform all
of
my calculations / If / Then statements using VBA but I'm not sure
how to
actually get the code to run or where to put it to run... I can
get all of
the code to run with an on click event or something like that but
basically
I'd like cells to automatically fill in other cells based on
values the
users
put in them. For example:

If sheet2!.cells(12,3) = "N" Then

Sheet3!.cells(3,2) = "Sold Out"

or

If sheet2!.cells(12,3) = "Y" Then

Sheet3!.Visible = False

I'll keep searching for the answers to this question but if
someone could
shoot me a quick answer I would appreciate it.