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

 

[EdwardMartinIII]

Ah...

Okay, actual movement. Cool, cool, and thank you for this so far.

So, I tried this:

Worksheets(ActiveSheet.Name).Range("m18").Copy Destination:=Worksheets(ActiveSheet.Name).Range("j18")

And it copies the value and the cell color and the actual button. Can't I copy only the value?


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]

Forms controls are much simpler than ActiveX controls, but the forms control can be used with the Caller.

Can I use a Form Control to modify the properties of an Active X control?


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]

Copy is Copy.

But you can Edit > Paste Special -- VALUES or other properties. Check it out.

You can modify ActiveX control's properties with code.

Skip,

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

 

[EdwardMartinIII]

But you can Edit > Paste Special -- VALUES or other properties. Check it out.

Ah, I meant programmatically.

You can modify ActiveX control's properties with code.

Not, apparently, code called with a Form Control button-press, unless I'm missing something.

I think I'm going to switch back to using an Active X control. For whatever reason, it seems the Form Control just doesn't want to manipulate or even see the other Active-X controls on the spreadsheet.

I guess the trick now is how does an Active-X button know where it is, cell-wise...


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]

Ah, I meant programmatically.

Check it out. WHat you can do on the sheet, you can ALSO do on code. Use your macro recorder if necessary.

Every shape on the sheet has a TopLeftCell property, among others.

Skip,

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

 

[EdwardMartinIII]

Every shape on the sheet has a TopLeftCell property, among others.

Aha! Figured out the right way to access this on an Active-X component.

So, I can programmatically determine the cell containing the button I just pressed, which gives me a value such as $M$18.

Is there a way I can modify an address and use it as a variable?

Specifically, what I want to do is perform some checks and manipulations in the same row, on a variety of cells. So, in the sample above ($M$18), I want to look at $C$18 (changing the M to a C), and if there is a value of 1, 2, 3, or 4, to change that value to 0.

Furthermore, if there is a button in that cell ($C$18), then get the name of that button and programmatically change properties of it.

The first part seems somewhat easy, once I know the trick of making up address names, but the second one looks... tricky.


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]

With ActiveSheet.Shapes("YourShapeName")
   With .TopLeftCell
      Select case Cells(.row, "C").Value
        Case 1, 2, 3, 4
           Cells(.row, "C").Value = 0
      End select
   End with
End with

Skip,

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

 

[EdwardMartinIII]

So, I would have to do this for every column? There is no way to loop through columns "C" through "H"?

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]

All you have to do is state your requirements up front...

dim i as integer

With ActiveSheet.Shapes("YourShapeName")
   With .TopLeftCell
      for i = 0 to 5
         Select case Cells(.row, "C").offset(0,i).Value
           Case 1, 2, 3, 4
              Cells(.row, "C").offset(0,i).Value = 0
         End select
      next
   End with
End with

Skip,

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

 

[EdwardMartinIII]

Ergh...

Can the control name be a variable? Or better yet, self-determined?

This is what I have:

Private Sub CButton0899_Click()
  Sheets("Req. A1").Select
  Range("j18").Select
  BName = "CButton0899"
  MsgBox "name of the button is " & BName
  MsgBox "The top left corner is over cell " & Worksheets("Req. A1").CButton0899.TopLeftCell.Address
  MsgBox "The value of that cell is " & Worksheets("Req. A1").CButton0899.TopLeftCell.Value
  If CButton0899.Caption = "Applicable" Then
    CButton0899.Caption = "Not Applicable"
    CButton0899.BackColor = vbRed
    Selection.Value = Worksheets("Req. A1").CButton0899.TopLeftCell.Value
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "C").Value
          Case 1, 2, 3, 4
            Cells(.Row, "C").Value = 0
        End Select
      End With
    End With
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "D").Value
          Case 1, 2, 3, 4
            Cells(.Row, "D").Value = 0
        End Select
      End With
    End With
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "E").Value
          Case 1, 2, 3, 4
            Cells(.Row, "E").Value = 0
        End Select
      End With
    End With
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "F").Value
          Case 1, 2, 3, 4
            Cells(.Row, "F").Value = 0
        End Select
      End With
    End With
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "G").Value
          Case 1, 2, 3, 4
            Cells(.Row, "G").Value = 0
        End Select
      End With
    End With
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        Select Case Cells(.Row, "H").Value
          Case 1, 2, 3, 4
            Cells(.Row, "H").Value = 0
        End Select
      End With
    End With
  Else
    CButton0899.Caption = "Applicable"
    CButton0899.BackColor = vbGreen
    Selection.Value = 0
  End If
End Sub

First question: how can I insert BName into the calls later in the routine? Alternately, is it possible to use some Active-X version of Application.Caller so that I don't even have to change the BName variable for each routine?

Second question: Is there a way to determine the name of an Active-X button contained by a cell described above? In my spreadsheet, if the cell contains a 1, 2, 3, or 4, it ALSO contains a button, which I would like to programmatically alter by pressing BName.


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]

Oh, this is much smaller:

Private Sub CButton0899_Click()
  Dim i As Integer
  Sheets("Req. A1").Select
  Range("j18").Select
  BName = "CButton0899"
  MsgBox "name of the button is " & BName
  MsgBox "The top left corner is over cell " & Worksheets("Req. A1").CButton0899.TopLeftCell.Address
  MsgBox "The value of that cell is " & Worksheets("Req. A1").CButton0899.TopLeftCell.Value
  If CButton0899.Caption = "Applicable" Then
    CButton0899.Caption = "Not Applicable"
    CButton0899.BackColor = vbRed
    Selection.Value = Worksheets("Req. A1").CButton0899.TopLeftCell.Value
    
    With ActiveSheet.Shapes("CButton0899")
      With .TopLeftCell
        For i = 0 To 5
          Select Case Cells(.Row, "C").Offset(0, i).Value
            Case 1, 2, 3, 4
            Cells(.Row, "C").Offset(0, i).Value = 0
          End Select
        Next
      End With
    End With

  Else
    CButton0899.Caption = "Applicable"
    CButton0899.BackColor = vbGreen
    Selection.Value = 0
  End If
End Sub

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]

No way that I am aware of.

Skip,

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

 

[SkipVought]

leaner yet...

Private Sub CButton0899_Click()
    Dim i As Integer
'[b]
    With CButton0899
'[/b]
        MsgBox "name of the button is " & .Name
        MsgBox "The top left corner is over cell " & .TopLeftCell.Address
        MsgBox "The value of that cell is " & .TopLeftCell.Value
    
       If .Caption = "Applicable" Then
            .Caption = "Not Applicable"
            .BackColor = vbRed
            Range("j18").Value = .TopLeftCell.Value
            
            With .TopLeftCell
              For i = 0 To 5
                With Cells(.Row, "C")
                    Select Case .Offset(0, i).Value
                      Case 1, 2, 3, 4
                        .Offset(0, i).Value = 0
                    End Select
                 End With
              Next
            End With
    
      Else
        .Caption = "Applicable"
        .BackColor = vbGreen
        Range("j18").Value = 0
      End If
   End With
  
End Sub

Skip,

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