Excel - a button referring/changing things in its own row 1

[EdwardMartinIII]

I'm pretty new to VBA, so please forgive me if this is obvious.

Background: I'm trying to build an inspection tool. This tool reviews hundreds of conditionals (for example, "all houses must have a door.") against methods of testing (for example, "Is item on the blueprints," "Was item purchased," "Has item been inspected by City," and so forth.

For each conditional, there are one or more verification tests (see my three examples above). There are a total of six different ways to verify stuff, but each condition usually only uses 1-3 of them, and different conditions have different requirements.

Sometimes, a condition is not applicable, so it's not scored.

Here's a more concrete example:

Condition: There must be a written document describing process X

Of the six different ways to verify this, methods 2, 3, and 4 apply. 1, 5, and 6 do not.

At the moment, I have a spreadsheet with my conditions as rows and my verification methods as columns.

If a particular condition requires, say, verification #3, then in that cell, I've placed a red button that currently reads "Not verified" and the cell contains a value of "0".

If you click the button, it turns green, changes the label to “Verified!” and changes the 0 value of that cell to whatever the value SHOULD be (in most cases, “1”, but could be other values including 2, 3, 4, etc. depending on how valuable the combination is to the overall score.

What I did for each of those 900+ buttons (again, please forgive me), is that I simply copied the previous code and changed the references:

Sheets("Req. A1").Select
Range("f18").Select

Doing that more than 900 times sucked.

What I’ve been asked to do now is to add, to each row, a button that the Inspector can click to negate an entire row, to essentially say that the Condition is not applicable. This button will make the row look “dimmer” and place a predetermined number in the “points being ixnayed” column (for each condition, this is anywhere from 1 to 7 points – I’ll have to transcribe those by hand). It’ll also check and if there are any green buttons in the row, and turn them red (using the same basic routine) and zero their values so that they aren’t inadvertently counted.

In my ideal world, there must be a smarter way than what I just did. There must be a smarter way to code a button I’ve pressed such that it kinda does this:

Sheets(IdentifySheetInWhichI’veClicked).Select
Range(IdentifyCellWhereButtonWasPressed).select
In the IdentifyCellWhereButtonWasPressed is a value. Copy that value to the Points Ixnayed column.
Look in B_ through H_ and if there’s a button present, do this:
{ change that button to red and change the value of that cell to “0”}

I can muddle along pretty well, but I don’t know how to determine, based on where the button was that was clicked, the “current” cell.

Is there a simple way to determine, if a button is clicked, what sheet/cell contains that button, and
Is there a way to use that data to create an on-the-fly algorithm to check certain ranges of cells also in that row, and
Is there a way to, if that button is clicked, to copy data from some other cell in that row to some other-other cell in that row, and
Apply some sort of color transformation that’s consistent to all cells of a certain range in that row (for example, adding, oh, 60 to each of the RGB colors).

It should be somewhat reversible, as well, but I figure that’s pretty simple once I have the other stuff nailed down. The only part that would cloud my mind is the color transformations on each cell. Right now, they are color-coded in different ways, and that color coding has to be preserved in both directions. That’s why I was thinking “add 60 to each value) because “subtract 60” gives you the original result back.

If reversed, I’m not at all worried about setting any individual buttons back to green, because the Inspector would have to reverify everything in that condition anyway, so buttons would stay red.

Ugh, I hope that wasn’t too long or complicated.

Thoughts? Ideas? Hints? I really don’t want to individually hand-code each routine...

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

hi,

What version of Excel?

What kind of controls did you use: MS Forms or Control Toolbox?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

Hi Skip,

It's Excel from Office 2007.

I'm not sure how to tell what kind of forms I used. Is there a way I can determine this? I think what I did was in the Developer tab, I clicked Insert >> Form Controls >> Button. I only did it once and since then simply copied each previous button.

If there's a way i can tell you, let me know.

Thanks!

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

Try this for which cell

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

hit submit too soon

Use this in any button like

Sub Button1_Click()
    MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address
End Sub

I assume you have MS Forms controls, unless your macro click event look like this...

Private Sub CommandButton1_Click()

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

Okay.

(hm, there is no "alert" window...?)

How might I refer to a cell that is on the same row, but -2 letters from that (basically, if the button is pressed at M18, how can I copy the value of the cell at M18 to, say, J18?

Thanks!


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

The macro Click event looks like this:

Private Sub CButton0898_Click()

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

"if the button is pressed at M18, how can I copy the value of the cell at M18 to, say, J18?"

    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
        .Copy Cells(.row, "J").
    End With

ie copy the [top left cell] to [top left cell] row, column J

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

I can get MsgBox to produce a "hello", but when I try

MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Address

I receive an error that reads "Run-time error '-2147352571 (80020005)': The item with the specified name wasn't found."



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Tryin' to debug what's going on...

This is my test code:

Private Sub CButton0899_Click()
  MsgBox "Active sheet is " & ActiveSheet.Name
  MsgBox "Active cell: " & ActiveCell.Address
  MsgBox "Application: " & Application
  Select Case TypeName(Application.Caller)
    Case "Range"
        v = Application.Caller.Address
    Case "String"
        v = Application.Caller
    Case "Error"
        v = "Error"
    Case Else
        v = "unknown"
End Select
MsgBox "caller = " & v

The last MsgBox produces "Error"

I cannot seem to figure out how to get it to produce the contents of the cell (in this case, .Value) that contains the button I just pressed.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

When I try

MsgBox "Value of cell: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Value

I receive "Run-time error - The item with the specified name wasn't found."

When I try

MsgBox "Value of cell: " & ActiveSheet.Shapes(Application.Caller).TopLeftCell

I receive "Run-time error - The item with the specified name wasn't found."

Other lines that don't seem to work include

MsgBox ActiveSheet.Shapes(Application.Caller).Name

(Run time error - The item with the specified name wasn't found.)

and

MsgBox "Application.Caller: " & Application.Caller

(Run-time error: type mismatch)


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

This runs from the Button Click event; ANY button click event.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

I would expect at least SOME of that to work, but I'm getting the errors listed. One of the lines (MsgBox ActiveSheet.Shapes(Application.Caller).Name) I even copied from another help site.

There must be some little thing I'm not doing right.

Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

TESTED!

This copies the cell where the button is to same row, column J

Sub Button1_Click()
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
    .Copy Cells(.Row, "J")
    End With
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You want to know how many CF cells are RED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EdwardMartinIII]

Skip, I appreciate your help, but this exact code:

Private Sub CButton0899_Click()
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
    .Copy Cells(.Row, "J")
    End With
End Sub

throws out the error:
Run-time error '-2147352571 (80020005)': The item with the specified name wasn't found.

Have I missed something?


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

I noticed mine says "Private" in the declaration. That seems to be the only difference, other than the button name.


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Okay, after a few updates and reboots (for unrelated reasons), and another project that ate my brain, I'm back.

I've copied this code from your example above, Skip:

Private Sub CButton0899_Click()
    With ActiveSheet.Shapes(Application.Caller).TopLeftCell
    .Copy Cells(.Row, "J")
    End With
End Sub

And when I run the sequence and click the button, the error is

Run-time error '-2147352571 (80020005)': The item with the specified name wasn't found.

I was hoping it wouldn't be.

When I click the [Debug] button, the listing highlights the line:
With ActiveSheet.Shapes(Application.Caller).TopLeftCell
as problematic.

All I'm trying to do is be able to identify which cell contains the button I just pressed, and make certain changes to cells lying to the left of it (as well as the button itself).

My test button here is in a cell M18 and has the value "3" in it.

Is there some obvious dum-dum thing I'm missing?

Thanks!



Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[EdwardMartinIII]

Ooooh...

I did not do this:
"I think what I did was in the Developer tab, I clicked Insert >> Form Controls >> Button"

I did this:

Insert >> ActiveX Controls >> Command Button.

Tried inserting the Form Controls Button and started seeing results...

Annoying.

I seem to have trouble accessing the properties of a Form Control button, such as its color or name. Is there a non-programmatic way to do this?


Edward

"Cut a hole in the door. Hang a flap. Criminy, why didn't I think of this earlier?!" -- inventor of the cat door

 

[SkipVought]

Forms controls are much simpler than ActiveX controls, but the forms control can be used with the Caller. Select the control and change the FONT color as you would the font in a cell. The Name can be assigned in the Name Box, to the left of the Formula Bar.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}