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.

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" <hshayh0rn@discussions.microsoft.com> wrote in message
news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> 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.

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" <hshayh0rn@discussions.microsoft.com> wrote in message
> news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > 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.
>
>
>

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" <hshayh0rn@discussions.microsoft.com> wrote in message
> > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > > 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.
> >
> >
> >

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" <hshayh0rn@discussions.microsoft.com> wrote in message
> > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > > > 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.
> > >
> > >
> > >

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" <hshayh0rn@discussions.microsoft.com> wrote in message
> > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >

did you run a macro like

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

--
Regards,
Tom Ogilvy

"hshayh0rn" <hshayh0rn@discussions.microsoft.com> wrote in message
news:7811B8AC-0ADD-4F01-B061-E98747268C39@microsoft.com...
> 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" <hshayh0rn@discussions.microsoft.com> wrote in message
> > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >

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" <hshayh0rn@discussions.microsoft.com> wrote in message
news:7811B8AC-0ADD-4F01-B061-E98747268C39@microsoft.com...
> 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" <hshayh0rn@discussions.microsoft.com> wrote in message
> > > > news:A01F93A5-C8A4-479D-AF14-E6A4DF0C0228@microsoft.com...
> > > > > 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.
> > > >
> > > >
> > > >