Thank you for the reply.
I guess I do have a bit of a wrench in the whole system. It does in
fact have a formula in B3 because it is pulling the answer from
another worksheet. It is a simple IF THEN statement, but nonetheless,
it is a formula. Is there a completely different approach I should be
trying?
Regards,
Jack
On Nov 11, 1:16 pm, "Asa Rossoff" <a...@lovetour.info> wrote:
> Hi Jack,
>
> Glad to help.
>
> Here's a corrected version of your Worksheet_Change event. As you have it
> now, I don't see how it will ever hide/unhide rows, as that If statement
> will always test as False. (Since Target.Address won't be exactly "B3" no
> matter what). Your Worksheet_Calculate routine has nothing to do with that,
> at least.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> With Range("B3")
>
> If Not Application.Intersect(.Cells, Target) Is Nothing Then
>
> Select Case .Value
>
> Case Is = "DSL-Cleaner"
>
> Rows("1:156").Hidden = False
>
> Rows("157:208").Hidden = True
>
> Rows("209:256").Hidden = False
>
> Case Is = "DSN-DSL Cleaner"
>
> Rows("1:256").Hidden = False
>
> End Select
>
> End If
>
> End With
>
> End Sub
>
> Be aware that VBA is case-sensitive by default, so the value in cell B3 will
> have to have the upper and lower case exactly as you specified. If that's a
> problem, you could use:
>
> Select Case LCase(.Value)
>
> Case Is = "dsl-cleaner"
>
> . . . .
>
> Case Is = "dsn-dsl cleaner"
>
> . . . .
>
> End Select
>
> As written, the only way rows 157:208 will be unhidden by this routine is if
> the value changes to "DSN-DSL Cleaner". If it changes to anything else,
> those rows will remain hidden since your routine does nothing for values
> other than these two. If you want something to happen for all other values,
> you can use
>
> Case Else
>
> In your Select.Case statement.
>
> --------
>
> Again, though, be aware that this code will not work as expected if cell B3
> contains a formula. Worksheet_Change does not respond to formulas changing
> values.
>
> It's confusing that you have two event routines dependent on the value of
> cell B3. One, Worksheet_Change, can only work if there is not a formula in
> B3, and the other, Worksheet_Calculate, will only happen at the time of a
> change of value in cell B3 if B3 DOES contain a formula.
>
> If you want both of these things to happen when the value of cell B3
> changes, both routines need to be in the same event; the Calculate event if
> it's a formula, or the Change event otherwise.
>
> Asa
>
>
>
>
>
>
>
> -----Original Message-----
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
>
> On Behalf Of Jack
> Sent: Friday, November 11, 2011 8:54 AM
> To: MS EXCEL AND VBA MACROS
> Subject: Re: $$Excel-Macros$$ Calling multiple type subroutines
>
> Thanks for the replying guys.
>
> Asa
>
> I'm still slightly confused on how to deploy the Select Case Range
>
> option that you suggested.
>
> Sam
>
> Great effort. However, due the fact that the picture subroutine was
>
> written in a Worksheet_Change routine, the Worksheet Calculate negates
>
> it ability to change the pictures. So none of my pictures appeared. Am
>
> I missing anything else to make this execute?
>
> Regards,
>
> Jack
>
> On Nov 11, 10:16 am, Sam Mathai Chacko <samde...@gmail.com> wrote:
>
> > I believe you can merge it like this....
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
>
> > Dim oPic As Picture
>
> > If Target.Address = "B3" Then
>
> > Select Case Target.Value
>
> > Case Is = "DSL-Cleaner"
>
> > Rows("1:156").Hidden = False
>
> > Rows("157:208").Hidden = True
>
> > Rows("209:256").Hidden = False
>
> > Case Is = "DSN-DSL Cleaner"
>
> > Rows("1:256").Hidden = False
>
> > End Select
>
> > Me.Pictures.Visible = False
>
> > With Target
>
> > For Each oPic In Me.Pictures
>
> > If oPic.Name = .Text Then
>
> > oPic.Visible = True
>
> > oPic.Top = .Top
>
> > oPic.Left = .Left
>
> > Exit For
>
> > End If
>
> > Next oPic
>
> > End With
>
> > End If
>
> > End Sub
>
> > Regards,
>
> > Sam Mathai Chacko
>
> > On Fri, Nov 11, 2011 at 1:57 PM, Asa Rossoff <a...@lovetour.info> wrote:
>
> > > Hi Jack,****
>
> > > ** **
>
> > > Does cell B3 contain a formula or are you entering a value directly?****
>
> > > ** **
>
> > > The Worksheet_Change event is triggered by changes that result from
> direct
>
> > > entry.****
>
> > > The Worksheet_Calculate event is triggered by worksheet calculation. If
>
> > > there is a formula in B3, it will never be the "Target" in a
>
> > > Worksheet_Change event, although any time it's resultant value changes
> (and
>
> > > even more often than that, most likely), you would get a
>
> > > Worksheet_Calculate event.****
>
> > > ** **
>
> > > Also, in your original post, the Worksheet_Change event you posted had a
>
> > > couple of potential problems that I noticed:****
>
> > > ** **
>
> > > (1) The subroutine declaration was commented out with a single
>
> > > apostrophe. The event would be disabled like that, and the VBA code
>
> > > without a Sub or Function declaration, and especially with an End Sub at
>
> > > the end, would most likely confuse VBA.****
>
> > > ** **
>
> > > (2) You have the statement:****
>
> > > If Target.Address = "B3" Then ....****
>
> > > ** **
>
> > > Since more than one cell can change at one time (copy/paste, fill, ?),
> the
>
> > > Address property will refer to all of the changed cells. Also, The
>
> > > .Address property will never = "B3". It could = "$B$3" though.****
>
> > > ** **
>
> > > The usual way of checking if a particular cell was changed in the
>
> > > Worksheet_Change event is to see if the cell intersects with Target:****
>
> > > If Not Application.Intersect(Range("B3"), Target) Is Nothing Then
> ....****
>
> > > ** **
>
> > > (3) You have the statement:****
>
> > > Select Case Target.Value****
>
> > > Case Is = "DSL-Cleaner"****
>
> > > ....****
>
> > > ** **
>
> > > If Target is a range of more than one cell, comparing Target.Value to a
>
> > > string will result in a type mismatch error, since Target.Value will be
> an
>
> > > array of values in that case. Admittedly this is inside your If
>
> > > Target.Address="B3" block, which if you changed to ="$B$3" would run
> your
>
> > > code if ONLY cell B3 was changed, and with only one cell changed, and
> your
>
> > > Select statement inside that If block it will never execute under other
>
> > > circumstances, and you would avoid an error. I recommend the Intersect
>
> > > method though. In which case, you might get a type mismatch, and also
> you
>
> > > would be checking the value of who knows what other cells you aren't
>
> > > interested in, too.****
>
> > > ** **
>
> > > You could use:****
>
> > > Select Case Range("B3").Value****
>
> > > ....****
>
> > > ** **
>
> > > Asa****
>
> > > ** **
>
> > > ** **
>
> > > ** **
>
> > > -----Original Message-----
>
> > > From: excel-macros@googlegroups.com
> [mailto:excel-macros@googlegroups.com]
>
> > > On Behalf Of Jack
>
> > > Sent: Thursday, November 10, 2011 12:12 PM
>
> > > To: MS EXCEL AND VBA MACROS
>
> > > Subject: Re: $$Excel-Macros$$ Calling multiple type subroutines****
>
> > > ** **
>
> > > I apologize for the lack of clarity. I blame it on being new to
> macros****
>
> > > still, lol.****
>
> > > ** **
>
> > > The first routine is a:****
>
> > > ** **
>
> > > Private Sub Worksheet_Calculate()****
>
> > > ** **
>
> > > Dim oPic As Picture****
>
> > > Me.Pictures.Visible = False****
>
> > > With Range("B3")****
>
> > > For Each oPic In Me.Pictures****
>
> > > If oPic.Name = .Text Then****
>
> > > oPic.Visible = True****
>
> > > oPic.Top = .Top****
>
> > > oPic.Left = .Left****
>
> > > Exit For****
>
> > > End If****
>
> > > Next oPic****
>
> > > End With****
>
> > > End Sub****
>
> > > ** **
>
> > > I forgot to mention that I pulled this routine from a web example.****
>
> > > ** **
>
> > > The problem I'm having is that when whichever routine I put first,****
>
> > > gets priority and it works. However the 2nd one does not. Putting
> the****
>
> > > picture change routine first, it does change the pictures. However,****
>
> > > the 2nd routine to hide rows does not work. I would just like to
> know****
>
> > > how to get them both to work since they are independent routines
> being****
>
> > > that they are 2 different types of sub routines****
>
> > > ** **
>
> > > Private Sub Worksheet_Calculate()****
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)****
>
> > > ** **
>
> > > I'm not sure how to work it to where both work simultaneously.****
>
> > > ** **
>
> > > Jack****
>
> > > ** **
>
> > > On Nov 10, 11:28 am, Sam Mathai Chacko <samde...@gmail.com> wrote:****
>
> > > > The Worksheet change routine is automatically triggered when there is
> a*
>
> > > ***
>
> > > > change (change in value/modification of value etc) in 1 or more
> cells***
>
> > > *
>
> > > > within THAT sheet. So you don't really CALL it, it GETS called, in
> other
>
> > > ****
>
> > > > words, TRIGGERED.****
>
> > > >** **
>
> > > > For the first routine, the context is not clear enough to judge when
> and
>
> > > ****
>
> > > > how it has to be called, so a bit more clarity may help****
>
> > > >** **
>
> > > > Regards,****
>
> > > > Sam****
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > >** **
>
> > > > On Thu, Nov 10, 2011 at 8:53 PM, Jack <aan...@gmail.com> wrote:****
>
> > > > > I am new to macros. I tried searching the internet for the answer
> but*
>
> > > ***
>
> > > > > the limited experience didn't quite help me find my answer. I hope
> you
>
> > > ****
>
> > > > > guys can figure it out.****
>
> > > >** **
>
> > > > > I have 2 sub routines. 1 that pulls a photo and another that
> hides****
>
> > > > > rows based on a value from cell B3. I tried to the Call option
> but****
>
> > > > > wasn't quite sure how to pull it together. Below are my 2
> routines:***
>
> > > *
>
> > > >** **
>
> > > > > Dim oPic As Picture****
>
> > > > > Me.Pictures.Visible = False****
>
> > > > > With Range("B3")****
>
> ...
>
> read more »
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
