Thread: ActiveCell and command button help

Hi all-

I have a spreadsheet that automatically analyzes the last week's worth of data and then displays a table of summary data from that analysis in the range C2:G11. Each row (i.e. 2:11) represents the statistics from one of the 10 regions in the state. I then have a second "details" table which remains blank (it occupies the range C14:G20; one row for each day of the week) until an "x" is typed in Column H in the cell adjacent to a region's summary data above, e.g. if I want to display detailed data for region 4 for the last week, I would type an "x" in cell H5; formulas in the blank "details" table then lookup information on region 4 from a larger dataset. This system works fine, but is a little clumsy (typing "x"'s and deleting previous "x"'s, etc.).

Here's what I would like:

1): A macro for this sheet that will place an "x" in the ActiveCell (only in the case that the active cell is somewhere in the range H2:H11), will remove that "x" when that cell is no longer active, and will not place more than one "x" at a time-- for instance if more than one cell is accidentally highlighted

2): Also, I would like to be able to paste the values and formatting of the "details" table (C14:G20) to a separate sheet (we'll call it Sheet2) if they're interesting, and I'd like to be able to do this with a command button. The problem is I don't know... 1: how to place a button on the sheet, 2: how to attach code to this button, 3: where to place this code (can it go in the same place as the ActiveCell macro I described earlier?). Ideally, the code would allow me to paste an arbitrary number of these tables to Sheet2, so the code would need to be able to identify the first blank cell in Column A as the location to paste a new table when the button is clicked.

Hope this makes sense (I am still pretty much a beginner with VBA and I have so far only used macros that are activated by a shortcut-- NOT the kind that occupy the module that's accessed by right clicking on the sheet tab and clicking View Code; these seem fundamentally different and I don't really know where to start) -- thanks a lot!

You can add a button simply from the Drawing menu and then right-click and assign macro.

I think it would help anyone responding here if you could attach a small sample workbook.

EDIT: you could do the first bit like this. Right-click on the sheet tab, view code and paste this:

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("H2:H11")) Is Nothing Then Exit Sub
Range("H2:H11").ClearContents
Target(1, 1).Value = "x"

End Sub

And perhaps the second bit could be done like this (assign this macro to a shape from the Drawing menu):

Code:

Sub xx()

Sheet1.Range("C14:G20").Copy
Sheet2.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats

End Sub