Relative addressing from named cell in VBA

[JenniferMurphy]

I have a little macro that I have assigned to a command button. It's basically working except for one detail. There are several named cells in the worksheet. Each one is a column header. I need to perform arithmetic on the cell immediately below each header. If the header is in B4, I need to do arithmetic on B5.

I need something like this:

Public Sub Tally()
Dim row as integer, col as integer
row=activesheet.cell("HdrA").row+1
col =activesheet.cell("HdrA").column
activesheet.cell(row, col) = activesheet.cell(row, col) + 1
End Sub

The above doesn't work. Can anyone help me with the correct syntax?

 

[VoG]

Try this

Public Sub Tally()
With Range("HdrA").Offset(1)
    .Value = .Value + 1
End With
End Sub

 

[JenniferMurphy]

Wow!!! That was fast and it works perfectly. Amazing. Thanks so much.

Can I ask one unrelated question?

The actual math I need to do depends on the results of an experiment. If I get one result, I do math of one set of cells. If I get another result, I do math on another set of cells.

I need a way to ask the user to specify the result, such as "High" or "Low". I can use MsgBox and ask "Was the result 'high'". The suer can select yes or no. But I would prefer to put up a message that has my choices ("High"/"Low") and allow the user to select one. It would be great if I could assign a keyboard character to each choice.

Thanks

 

[VoG]

Unfortunately a regular MsgBox limits the buttons to Yes, No, OK and Cancel.

One way would be a simple UserForm with two buttons - High and Low. Or you could use an Add-in like ktMsgBox http://www.h3.dion.ne.jp/~sakatsu/ktMsgBox_Series_English.htm

 

[JenniferMurphy]

That's what I was afraid of. I couldn't find anything about defining my own buttons.

I defined a second command button. I now have one labeled "High" and one labeled "Low". Is there a way to have the command button pass a parameter to the macro?

I tried changing the command button assignment statement to:

Test.xlsm!Sheet1.Tally(1)

but that got an error message about the formula being too complex.

I rewrote the macro to:

Public Sub Tally(Result As Boolean)
 
If Result Then
  With Range("TestHigh").Offset(1)
    .Value = .Value + 1
  End With
Else
  With Range("TestLow").Offset(1)
    .Value = .Value + 1
  End With
End If
 
End Sub

Is there a way to pass a parameter?

If not, I'll have each one call a different macro and have that one call the common macro with a parameter.

Thanks

 

[VoG]

Try like this. In a regular Module

Public Result As Boolean
Public Sub Tally()
 
If Result Then
  With Range("TestHigh").Offset(1)
    .Value = .Value + 1
  End With
Else
  With Range("TestLow").Offset(1)
    .Value = .Value + 1
  End With
End If
 
End Sub

And in the form's code module use code like

Private Sub CommandButton1_Click()
Result = True
End Sub

Private Sub CommandButton2_Click()
Result = False
End Sub

 

[VoG]

In fact it should be more like

Private Sub CommandButton1_Click()
Result = True
Unload Me
Call Tally
End Sub

Private Sub CommandButton2_Click()
Result = False
Unload Me
Call Tally
End Sub

 

[JenniferMurphy]

Do you like that better than passing a parameter to Tally:

Private Sub CommandButton1_Click()
Unload Me
Call Tally True
End Sub
 
Private Sub CommandButton2_Click()
Unload Me
Call Tally False
End Sub

What does the Unload command do?

 

[VoG]

That would need to be

Call Tally(False)

Which way you do it is largely down to personal preference.

Unload Me removes the userform from memory. If you wanted to reuse it then you could use Me.Hide instead.

 

[JenniferMurphy]

Is there a way within the macro to get the name of the button that was clicked?