Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Select Cells by Clicking One Cell (2000 (SP-3))

    Is it possible to put a formula in a cell that when it is selected it will select other cells?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    I don't understand your question. Could you explain in more detail, what you are trying to accomplish?

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    Worksheet functions can't perform actions like that.

    You could use the Worksheet_BeforeDoubleClick event to select some cells when you double click a cell. For example: you enter the text B3:C8 in cell A1. When you double click A1, cells B3:C8 are selected. To create the code for this:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11)
    <LI>Activate the Project Explorer (Ctrl+R)
    <LI>If necessary, expand the workbook until you see the name of the worksheet containing the cell.
    <LI>Double click the name of the workbook.
    <LI>Type the following code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1") <> "" Then
    Range(Range("A1")).Select
    Cancel = True
    End If
    End If
    End Sub

    <LI>Switch back to Excel[/list]But, like Steve asked, what do you want to accomplish? Something like the above seems superfluous.

  4. #4
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    I have a cell that there is a number in (say A4). If I select that cell, I'd like a macro to select other cells (i.e. C7:K14,N7:N14,O8) and clear the contents of them then be able to type in A4.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    What we have is a form that is filled in, printed, then saved. Next week that same form needs to be done again and user was asking if there was a way that if he select a cell (A4) that it would then select other cells (C7:K14,N7:N14,O8), clear them out, and let him type information in them again.

    I stumbled through creating a maco that I'd have to play while in A4 but I'm wondering if I can bypass having to play it, that just while selecting A4 it will trigger the macro to run.

    Sub SelectCells()
    '
    ' SelectCells Macro

    ActiveCell.Select
    Range("B1,B2,B3,C4,C5").Select
    Selection.ClearContents
    End Sub



    Hope this is a little clearer.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    You could use the SelectionChange worksheet event for this, but I don't think it is desirable. You wouldn't be able to do anything with the cell any more - as soon as you select the cell, you are whisked away. But you don't need to select other cells to clear them.

    Here is code that just clears the cells, without selecting them. You can create it the same way I described in my previous reply:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A4")) Is Nothing Then
    Range("B1,B2,B3,C4,C5").ClearContents
    End If
    End Sub

    I don't like the effect this has, but it's a matter of personal taste, I suppose.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    Does it HAVE to be a cell? Why not add a command Button with a label like "Clear Entries" assigned to the macro?:

    <pre>Sub ClearCells()
    Range("B1,B2,B3,C4,C5").ClearContents
    End Sub
    </pre>



    Steve

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3)

    You could truly automate the process with a macro that runs on workbook open:

    Private Sub Workbook_Open()
    'This macro runs whenever you open the workbook
    WDay = Weekday(Now(), vbMonday) 'Picks out the day of the week, with Monday =1
    If WDay > 4 Then 'If Wday is 5, then TGIF, and time to clear the range of cells.
    Which = MsgBox("Do you want to clear the cells now?", vbQuestion + vbYesNo) 'Last chance
    If Which = vbYes Then Range(RANGE_TO_CLEAR).ClearContents
    End If
    End Sub

    Of course, change the day of the week & the logic to fit your situation.

    Errol

  9. #9
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    Hey, Leanne! Have you considered using an Excel Template?

    You just take a "virgin" copy of the spreadsheet (all cleared out and ready to go) and save it with the extension .XLT instead of .XLS.

    When your user double-clicks on it, he'll get a fresh copy ready to fill in. The template remains untouched. He just gets a copy of it -- unnamed and unsaved, so he'll have to do that himself. Every week he could pop open a new copy, save it with the date in the file name and never again have to keep cleaning up and fiddling with a "used" copy.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  10. #10
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3)

    Thank you Sister Dory! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> I bet while we were all looking at macros and VBA, all Leanne needed was a template. The template will never be "messed up", and all the permanent information will be ready and the correct blank fields will be blank when you open a new copy.

    If you've never used a template, here's some hints: When you Save As and select *.xlt in the Save as type, you'll automatically be switched to save the *.xlt file in the Excel Template folder. (You'll never see the template file unless you navigate to the template folder.) To open a new template, simply click on File|New..., NOT the little white square in the upper left corner. Then select the worksheet template. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    Errol

  11. #11
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    A shortcut on the user's desktop or Office Shortcut Bar usually helps folks get a fresh copy of the template without tears, too.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  12. #12
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    Wisconsin, USA
    Posts
    193
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    First of all, thanks to everyone for suggestions. After leaving work Friday, I too thought about a template but user informed me this morning that he doesn't always need a clean slate every time he starts. I'm going to attach a sample of what I'm referring to. I did get a macro to work that when selecting A8 and running the macro, it will select/clear correct cells; however, I need this to also work when selecting cells A19 and A31 so I believe I'd need 3 separate macros. Not bad, but the user has created 55 pages of this form (copy/paste...).

    If I'm asking for the impossible, the user said he can accept that but just thought there may be a way to do it.

    By the way Dory, what part of the state is Holstein Falls located in?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    You could handle this in the SelectionChange macro I posted in a previous reply.

    If you have regions of different size, you could handle them in an If Then ElseIf statement:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A8")) Is Nothing Then
    Range("C7:L14").ClearContents
    ElseIf Not Intersect(Target, Range("A19")) Is Nothing Then
    Range("C18:K26").ClearContents
    ElseIf Not Intersect(Target, Range("A31")) Is Nothing Then
    Range("D30:L37").ClearContents
    End If
    End Sub

    If you want to clear an area of fixed size with a fixed position relative to the cell that triggers the macro, it can be done in one step:

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A8,A19,A31")) Is Nothing Then
    Target.Offset(-1, 2).Range("A1:J8").ClearContents
    End If
    End Sub

    If you want to do this in multiple sheets, use the Workbook_SheetSelectionChange event in ThisWorkbook instead of the SelectionChange event of each individual worksheet. SheetSelectionChange has 2 arguments: sh represents the active worksheet, and Target the cell(s) selected by the user.

  14. #14
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    Leanne:

    This macro will clear out the ranges for ANY grid in the workbook. Since the user cuts & pastes them, I'm assuming all 55 look the same in layout.
    The user must have a "Station" cell selected to make sure you're in the right position to use Offset. I formatted cell A7 with yellow and bold to make it an obvious "hotspot" and then Format...Style...[type the name of the new style]...OK. I called the style "Station" in this case. Then you can apply that style to all the othr 54 places you need it.

    Add a toolbar button that runs the macro below. If the user is on one of the hotspots, the macro will clear all the data from that section. Only ONE MACRO!

    <pre>Sub ClearTheDeck()
    'Use a Style called "Station" to identify the top left cell of the range to clear
    'I used yellow and bold to make it an obvious hotspot.

    If Selection.Style = "Station" Then

    'OPTIONAL: Make sure the user really wants to whack their data
    bSure = MsgBox("Are you sure you want to clear this section of data?", vbYesNo, "Clear Data")
    If bSure = vbNo Then Exit Sub

    'C7:K14 or it's equivalent on other rows
    Range(Selection.Offset(0, 2), Selection.Offset(7, 10)).ClearContents
    'N7:N14
    Range(Selection.Offset(0, 13), Selection.Offset(7, 13)).ClearContents
    'O8
    Selection.Offset(1, 14).ClearContents
    End If

    End Sub</pre>


    Uh...Holstein Falls -- the Cow Tipping Capital of the World! -- is more of a state of mind, really. Not too far from Madison, Wisconsin you could say. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts

    Re: Select Cells by Clicking One Cell (2000 (SP-3))

    rather than..
    If bSure = vbNo Then Exit Sub
    ..better to use
    If bSure <> vbYes Then Exit Sub

    ..in case User presses [Esc] key

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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