[RESOLVED] Clear Range of Cells After Asking To Click Starting Cell

[geelsu]

I've been working on a spreadsheet to do my budget and found a sequence of code that allows you to make a cell in the spreadsheet do something when you click on it. Most of you have probably already seen this type code, as shown below, in one flavor or another.

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          If Selection.Count = 1 Then
              ' Enter New Transactions for an account. These ranges contain the clickable cell
              ' that initiates the transaction.
               If Not Intersect(Target, Range("L41,R41,X41")) Is Nothing Then
               Call New_Transaction

Now I am trying to do the same kind of thing to clear a range of cells. It is easy enough for me to just highlight the range, right click and then Clear Contents. But I'm trying to automate the budget spreadsheet so others can easily use it who have less Excel experience.

I'll have Cells in the spreadsheet filled with "Clear Transaction" with code for those addresses in the module above to call a Clear_Transaction macro. The part I'm having difficulty finding examples for is how to incorporate a MsgBox (or something) asking a question like "Click on the first Cell of the transaction" which will then make that "first Cell" the active cell and then the macro will clear it and the 5 cells to the right in one quick operation.

I might be approaching this wrong. Obviously you can tell I am no visual basic expert and have been finding example code on line, putting it in my spreadsheet, tweaking it a bit until it works and then doing a File - Save before I break it with subsequent tweaking.

So basically I'm trying to clear a line by first having someone select the starting cell via a MsgBox or some other input method and then clear that cell and the next 5 to the right.

According to my Help -> About I am using Microsoft Visual Basic for Applications version 7.0. Please forgive me if I have posted this in the wrong place. If I have, Moderators, please relocate for me if you don't mind.

Thank you very much for any input or ideas you may have.
They will be greatly appreciated.

Kind regards.

[si_the_geek]

Welcome to VBForums

According to my Help -> About I am using Microsoft Visual Basic for Applications version 7.0. Please forgive me if I have posted this in the wrong place. If I have, Moderators, please relocate for me if you don't mind.

No problem...

You are using VBA, so I've moved the thread from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[westconn1]

So basically I'm trying to clear a line by first having someone select the starting cell via a MsgBox or some other input method and then clear that cell and the next 5 to the right.

not quite what you asked, but you can try like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
          If target.column = 1 Then
              if msgbox "do you want to clear " & target.resize(,5).address, vbOKCancel) = vbOK Then target.resize(,5).clearcontents
          end if

i am assuming that (I'll have Cells in the spreadsheet filled with "Clear Transaction") are in column 1, change to suit

of course if you have multiple options of what you want to do, depending on which cell is clicked, you will have to have some criteria based on the column or range of the clicked cell

i am not sure how you can have a messagebox to ask the user to select the cell before you select the cell, unless you have a delete transaction button somewhere

[geelsu]

I think a picture would help. I have attached a screenshot of one of the areas of my spreadsheet. There is a line that shows "mulching and yard cleanup." That is a good entry for this account, Stewarts, which is shown at the top in the grey area. But the line for groceries is a mistake. In the lower right is Remove Transaction, Home, and New Transaction. The Home and New Transaction are currently locked and loaded i.e. working properly. So in this example, I would click Remove Transaction, which I am seeking help coding. There would be a popup that asks, "Which Transaction." I would then be able to click on the 19 Mar 2017 entry for groceries to remove it, because a groceries entry does not belong in the landscaping account, but should go in the groceries account. It would removed HG30 through HD30

[vbfbryce]

something like:

Private Sub btnRemove_Click()
    Dim val As Integer
    val = MsgBox("Remove data in 6 cells, starting in cell " & ActiveCell.Address & "?", vbYesNo)
    If val = 6 Then '6=Yes
        Range(ActiveCell, ActiveCell.Offset(0, 5)).Clear
    Else
        'didn't click yes
    End If
End Sub

[geelsu]

Thanks vbfbryce. That code is very close. Nice!!!! I have attached an image of what it is doing, but here is the synopsis: I made HH35 the clickable cell to Remove Transactions. When I click on it, the active cell becomes HH35 and it wants to delete 6 cells starting at that point. So I guess HH35 to HM35 would be zapped. If I pre-select HD30 i.e the transaction dated 17 Mar 2017 for groceries and then click Remove Transaction, it still wants to start the delete at HH35. Any ideas how to grab HD30 and through HG30 AFTER clicking Remove Transaction in HH35.

?

[vbfbryce]

I'm not sure what you mean by "...the active cell becomes..."

The active cell is whatever cell has the focus before you click the button. I tested it in various places, and it always cleared the cells starting with the active cell, then going to the right. It feels like your code must differ from mine, or you don't have a cell selected each time before running it.

[geelsu]

So it must be related to how I am defining the "Remove Transaction" cell. Here is what I have in the code that runs the "click" on those cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
' Enter New Transactions for an account. These ranges contain the clickable cell
' that initiates the transaction.
        If Not Intersect(Target, Range("L41,R41,X41")) Is Nothing Then
            Call New_Transaction

... snip out a bunch of stuff here that repeats my New_Transaction call at various Cells ....
... Next and further down is my "Remove Transaction" code.  So far it points to only two
    defined cells ...

' Remove Transaction
        ElseIf Not Intersect(Target, Range("L35")) Is Nothing Then
            Call Remove_Transaction
        ElseIf Not Intersect(Target, Range("HH35")) Is Nothing Then
            Call Remove_Transaction

My limited understanding of this was that if I click L35 or HH35 it would run the Remove Transaction module ... which contains your graciously provided code. I renamed your SUB to Remove_Transaction. I guess what happens is this is actually "refocusing" the active cell to L35 or HH35, not matter where I click. Perhaps I cannot use this technique to remove the transaction unless I assign your code a Macro Key Sequence which I was trying to avoid if at all possible. I guess I could have Remove Transaction pop up a message box that says, "Highlight the Date field of the transaction you want to remove and press Control-Shift-B" which would then run your code.

[vbfbryce]

In the attached, click on a filled cell in column B, then click the button.

[geelsu]

That, my friend, is absolutely fantastic. Perfect!!!! Now I'll have to google how to make a button like that. I've seen those before, but never have explored the mechanics behind them. I like that button so much better than what I am trying to do. Thank you so much.

[vbfbryce]

To make the button, you need the Developer tab visible. If you don't already have it:

https://support.office.com/en-us/art...B-E5FD9BEA2D45

Once you have it, click on it, then select Insert, and add an "Active X button." You go into "design mode" to double click it to tie code to it.

[geelsu]

Hi. Sorry for the delay ... lunch and workout. I found the Developer tab and was able to look at your code. Thanks again for your help. It is greatly appreciated.

[vbfbryce]

Any time.