Excel Macro Help

Question

I'm looking for assistance in creating an excel macro with the following two functions:

• Increment or decrement a value based on an action of another field. For example. cell b2 starts out with "0", it will either increment or decrement by "1" if another cell is clicked. Another way is say I have in cell D2, a two way arrow and one end of the arrow is clicked, it will increment the value in B2 by one, if the other end is clicked, it will decrease the value in b2 by one. Optionally, in cell D2 and D3 are command buttons and clicking on them will change the value in B2.

• Input and store a value based on clicking another field. For example, cell e2 is where the values are displayed. In cell e4, e5, e6, e7, etc., I have the values "A", "B", C", "D", etc.. If I click "A", that value is stored in e2, when I click "B", that value is stored in e2, if I click "A" again, it is added to e2. When I look in cell e2, I should see ABA. It would be nice to have the values counted and I see A=2 and B=1.

Anyone know of a source or reference that I can view for such examples?

Answer 1

Insert two buttons in D2, label them with arrows as you please, give them relevant names, then doubleclick them and add code similar to the following:

Private Sub Nameoftheupbutton_Click() 
    Sheets("nameofyoursheet").Range("B2").Value = 
      Sheets("nameofyoursheet").Range("B2").Value + 1 
End Sub 

Private Sub Nameofthedownbutton_Click() 
    Sheets("nameofyoursheet").Range("B2").Value = 
      Sheets("nameofyoursheet").Range("B2").Value - 1 
End Sub

and for your set-values buttons, insert, label, and name as above, then:

Private Sub NameoftheAbutton_Click() 
    Sheets("nameofyoursheet").Range("E2").Value = "A"
End Sub 

Private Sub NameoftheBbutton_Click() 
    Sheets("nameofyoursheet").Range("E2").Value = "B"
End Sub

Answer 2

This question should probably be tagged 'VBA' as well