+ Reply to Thread
Results 1 to 15 of 15

How to 'Click and then perform action'

  1. #1
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143

    How to 'Click and then perform action'

    Hello.

    Just a quick one. I need a code for the following action:

    Click on cell
    Zoom in by 'X' percent
    then on 'OffClick' to zoom out again to 'X' Percent

    (the reason i need this is that i am using a pull down list which at 50% is impossible to read)

    Thanks.

  2. #2
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    Try worksheet selection change event (in the worksheet module)
    (set $A$1 to your cell, use $ signs) (set the zoom values to whatever)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    ActiveWindow.Zoom = 100
    Else: ActiveWindow.Zoom = 50
    End If
    End Sub

    --
    steveB

    Remove "AYN" from email to respond
    "chris100" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello.
    >
    > Just a quick one. I need a code for the following action:
    >
    > Click on cell
    > Zoom in by 'X' percent
    > then on 'OffClick' to zoom out again to 'X' Percent
    >
    > (the reason i need this is that i am using a pull down list which at
    > 50% is impossible to read)
    >
    > Thanks.
    >
    >
    > --
    > chris100
    > ------------------------------------------------------------------------
    > chris100's Profile:
    > http://www.excelforum.com/member.php...o&userid=25166
    > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    >




  3. #3
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    SteveB,

    Works like a charm but for one thing, the actual cell i want to use this on is a merged cell (C7-E7) and so for the code you wrote doesn't work.
    Is there a way around this? i notice it runs through part of the operation but not all of it.

    regards

  4. #4
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    Hi all

    Thanks for the help SteveB but i found the problem was that the cell i wanted to zoom in on was a List used for validation. The exact answer to my problem i found from somewhere else:

    Debra Dalgleish suggested a macro like this:

    Option Explicit
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    If Intersect(Target, Range("a1,b3,d9")) Is Nothing Then
    ActiveWindow.Zoom = 40
    Else
    ActiveWindow.Zoom = 120
    End If

    End Sub

    I wouldn't have found the solution without the basics to start with. Hope this helps anyone else having a similar problem.

  5. #5
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    add
    msgbox.target.address
    in your code. This will let you know how excel sees the merged cell.

    For selection change:
    address = "$C$7:$E$7"

    see if that doesn't help...
    --
    steveB

    Remove "AYN" from email to respond
    "chris100" <[email protected]> wrote in
    message news:[email protected]...
    >
    > SteveB,
    >
    > Works like a charm but for one thing, the actual cell i want to use
    > this on is a merged cell (C7-E7) and so for the code you wrote doesn't
    > work.
    > Is there a way around this? i notice it runs through part of the
    > operation but not all of it.
    >
    > regards
    >
    >
    > --
    > chris100
    > ------------------------------------------------------------------------
    > chris100's Profile:
    > http://www.excelforum.com/member.php...o&userid=25166
    > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    >




  6. #6
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    Hi SteveB,

    I tried adding as suggested but came up with the error message "Argument not optional" highlighting the 'Msgbox' part in the second line. What am i doing wrong?

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If MsgBox.Target.Address = "$C$7:$E$7" Then
    ActiveWindow.Zoom = 100
    Else: ActiveWindow.Zoom = 50
    End If
    End Sub

  7. #7
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    Remove "MsgBox"
    I used
    MsgBox target.address
    so that you could see what the address is when you make a selection (a
    double check to
    help you see what is happening). You don't need it...

    and just use
    If target.Address =

    --
    steveB

    Remove "AYN" from email to respond
    "chris100" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi SteveB,
    >
    > I tried adding as suggested but came up with the error message
    > "Argument not optional" highlighting the 'Msgbox' part in the second
    > line. What am i doing wrong?
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If MsgBox.Target.Address = "$C$7:$E$7" Then
    > ActiveWindow.Zoom = 100
    > Else: ActiveWindow.Zoom = 50
    > End If
    > End Sub
    >
    >
    > --
    > chris100
    > ------------------------------------------------------------------------
    > chris100's Profile:
    > http://www.excelforum.com/member.php...o&userid=25166
    > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    >




  8. #8
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    Spot on Steve! For anyone that's interested, the below is a macro to zoom to 100% when a merged cell is selected (c7:e7). Once you click anywhere else it magnifies back to 50% for a better view of the whole screen. The reason this is used is that as a pull down list, the text is very small in 50% view so you need to zoom back for the overall picture:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$C$7:$E$7" Then
    ActiveWindow.Zoom = 100
    Else: ActiveWindow.Zoom = 50
    End If
    End Sub


    Thanks to all who helped, especially SteveB who has been a great support.

  9. #9
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    Chris,

    Glad I helped ...

    The real credit goes to this ng and all the things they have taught me...

    (also the things I learn from recording macros)

    keep on Exceling...
    --
    steveB

    Remove "AYN" from email to respond
    "chris100" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Spot on Steve! For anyone that's interested, the below is a macro to
    > zoom to 100% when a merged cell is selected (c7:e7). Once you click
    > anywhere else it magnifies back to 50% for a better view of the whole
    > screen. The reason this is used is that as a pull down list, the text
    > is very small in 50% view so you need to zoom back for the overall
    > picture:
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Target.Address = "$C$7:$E$7" Then
    > ActiveWindow.Zoom = 100
    > Else: ActiveWindow.Zoom = 50
    > End If
    > End Sub
    >
    >
    > Thanks to all who helped, especially SteveB who has been a great
    > support.
    >
    >
    > --
    > chris100
    > ------------------------------------------------------------------------
    > chris100's Profile:
    > http://www.excelforum.com/member.php...o&userid=25166
    > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    >




  10. #10
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    Regards all.
    As an extention of this i had two different cells that i wanted to zoom in on in the same way.

    The first, as above, works fine but when i use the same code for another cell it fails. I'm sure this is just something to do with how i'm declaring the sub and tried changing the name - but unfortunately i'm still a looong way from being an expert at this sort of thing!

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$M$5:$O$5" Then
    ActiveWindow.Zoom = 110
    Else: ActiveWindow.Zoom = 35
    ActiveWindow.ScrollColumn = 1
    End If
    End Sub

    Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    If Target.Address = "$c$69:$d$69" Then
    ActiveWindow.Zoom = 110
    Else: ActiveWindow.Zoom = 35
    ActiveWindow.ScrollColumn = 1
    End If
    End Sub

    I'm sure this is something relatively simple so if anyone could help please

  11. #11
    Forum Contributor
    Join Date
    02-26-2005
    Posts
    175
    Chris,

    If you are trying to put both of these into the same Sheet Module,
    its my understanding you can not put two "Worksheet_SelectionChange"
    into the same module.
    You have to combine the code into one
    "Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

    this help?
    Dave
    Quote Originally Posted by chris100
    Regards all.
    As an extention of this i had two different cells that i wanted to zoom in on in the same way.

    The first, as above, works fine but when i use the same code for another cell it fails. I'm sure this is just something to do with how i'm declaring the sub and tried changing the name - but unfortunately i'm still a looong way from being an expert at this sort of thing!

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$M$5:$O$5" Then
    ActiveWindow.Zoom = 110
    Else: ActiveWindow.Zoom = 35
    ActiveWindow.ScrollColumn = 1
    End If
    End Sub

    Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    If Target.Address = "$c$69:$d$69" Then
    ActiveWindow.Zoom = 110
    Else: ActiveWindow.Zoom = 35
    ActiveWindow.ScrollColumn = 1
    End If
    End Sub

    I'm sure this is something relatively simple so if anyone could help please

  12. #12
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    Your code is looking for a selection of more than a single cells.

    try the Intersect method:

    If Intersect(Target.Address, Range("A1:M5")) Then
    MsgBox "It's here"
    Else: MsgBox "You're not there yet"
    End If

    --
    steveB

    Remove "AYN" from email to respond
    "chris100" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Regards all.
    > As an extention of this i had two different cells that i wanted to zoom
    > in on in the same way.
    >
    > The first, as above, works fine but when i use the same code for
    > another cell it fails. I'm sure this is just something to do with how
    > i'm declaring the sub and tried changing the name - but unfortunately
    > i'm still a looong way from being an expert at this sort of thing!
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > If Target.Address = "$M$5:$O$5" Then
    > ActiveWindow.Zoom = 110
    > Else: ActiveWindow.Zoom = 35
    > ActiveWindow.ScrollColumn = 1
    > End If
    > End Sub
    >
    > Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    > If Target.Address = "$c$69:$d$69" Then
    > ActiveWindow.Zoom = 110
    > Else: ActiveWindow.Zoom = 35
    > ActiveWindow.ScrollColumn = 1
    > End If
    > End Sub
    >
    > I'm sure this is something relatively simple so if anyone could help
    > please
    >
    >
    > --
    > chris100
    > ------------------------------------------------------------------------
    > chris100's Profile:
    > http://www.excelforum.com/member.php...o&userid=25166
    > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    >




  13. #13
    Dave Peterson
    Guest

    Re: How to 'Click and then perform action'

    I bet you meant:

    If Intersect(Target, Range("A1:M5")) Then

    (w/o the .address)

    STEVE BELL wrote:
    >
    > Your code is looking for a selection of more than a single cells.
    >
    > try the Intersect method:
    >
    > If Intersect(Target.Address, Range("A1:M5")) Then
    > MsgBox "It's here"
    > Else: MsgBox "You're not there yet"
    > End If
    >
    > --
    > steveB
    >
    > Remove "AYN" from email to respond
    > "chris100" <[email protected]> wrote in
    > message news:[email protected]...
    > >
    > > Regards all.
    > > As an extention of this i had two different cells that i wanted to zoom
    > > in on in the same way.
    > >
    > > The first, as above, works fine but when i use the same code for
    > > another cell it fails. I'm sure this is just something to do with how
    > > i'm declaring the sub and tried changing the name - but unfortunately
    > > i'm still a looong way from being an expert at this sort of thing!
    > >
    > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > If Target.Address = "$M$5:$O$5" Then
    > > ActiveWindow.Zoom = 110
    > > Else: ActiveWindow.Zoom = 35
    > > ActiveWindow.ScrollColumn = 1
    > > End If
    > > End Sub
    > >
    > > Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    > > If Target.Address = "$c$69:$d$69" Then
    > > ActiveWindow.Zoom = 110
    > > Else: ActiveWindow.Zoom = 35
    > > ActiveWindow.ScrollColumn = 1
    > > End If
    > > End Sub
    > >
    > > I'm sure this is something relatively simple so if anyone could help
    > > please
    > >
    > >
    > > --
    > > chris100
    > > ------------------------------------------------------------------------
    > > chris100's Profile:
    > > http://www.excelforum.com/member.php...o&userid=25166
    > > View this thread: http://www.excelforum.com/showthread...hreadid=388140
    > >


    --

    Dave Peterson

  14. #14
    STEVE BELL
    Guest

    Re: How to 'Click and then perform action'

    Dave,

    Thanks for catching my baa-boo....

    (suffering from 'address on the mind')

    --
    steveB

    Remove "AYN" from email to respond
    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    >I bet you meant:
    >
    > If Intersect(Target, Range("A1:M5")) Then
    >
    > (w/o the .address)
    >
    > STEVE BELL wrote:
    >>
    >> Your code is looking for a selection of more than a single cells.
    >>
    >> try the Intersect method:
    >>
    >> If Intersect(Target.Address, Range("A1:M5")) Then
    >> MsgBox "It's here"
    >> Else: MsgBox "You're not there yet"
    >> End If
    >>
    >> --
    >> steveB
    >>
    >> Remove "AYN" from email to respond
    >> "chris100" <[email protected]> wrote
    >> in
    >> message news:[email protected]...
    >> >
    >> > Regards all.
    >> > As an extention of this i had two different cells that i wanted to zoom
    >> > in on in the same way.
    >> >
    >> > The first, as above, works fine but when i use the same code for
    >> > another cell it fails. I'm sure this is just something to do with how
    >> > i'm declaring the sub and tried changing the name - but unfortunately
    >> > i'm still a looong way from being an expert at this sort of thing!
    >> >
    >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    >> > If Target.Address = "$M$5:$O$5" Then
    >> > ActiveWindow.Zoom = 110
    >> > Else: ActiveWindow.Zoom = 35
    >> > ActiveWindow.ScrollColumn = 1
    >> > End If
    >> > End Sub
    >> >
    >> > Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
    >> > If Target.Address = "$c$69:$d$69" Then
    >> > ActiveWindow.Zoom = 110
    >> > Else: ActiveWindow.Zoom = 35
    >> > ActiveWindow.ScrollColumn = 1
    >> > End If
    >> > End Sub
    >> >
    >> > I'm sure this is something relatively simple so if anyone could help
    >> > please
    >> >
    >> >
    >> > --
    >> > chris100
    >> > ------------------------------------------------------------------------
    >> > chris100's Profile:
    >> > http://www.excelforum.com/member.php...o&userid=25166
    >> > View this thread:
    >> > http://www.excelforum.com/showthread...hreadid=388140
    >> >

    >
    > --
    >
    > Dave Peterson




  15. #15
    Forum Contributor
    Join Date
    07-12-2005
    Posts
    143
    Thanks guys but i think i didn't make myself clear. I wanted to have two cells that zoom in and out on the same sheet but in different places. I figured it out with the basics you gave me before - or i needed was an ElseIf. Doh!

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$C$7:$E$7" Then
    ActiveWindow.Zoom = 110
    ElseIf Target.Address = "$B$61:$C$61" Then
    ActiveWindow.Zoom = 110
    Else: ActiveWindow.Zoom = 40
    End If
    End Sub

    Thanks for the input guys and gals.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1