title: Streamline VBA to Change shape color on click in excel
body: <p>I have a ‘form’ that contains set of questions on a worksheet (note this is not a userform and I don’t want to use one). 
  Some answers are yes/no, others have multiple answers such as quantity (ie and the answer may be 1 or 2 or 3 or 4 etc).</p>
  
  <p>The design of the ‘form’ on this worksheet calls for these answers to be shapes which the user clicks like a button to select their answer -  Pls note I do not want to use Command buttons. </p>
  
  <p>In this simple example I have 2 rectangle shapes one name “yes” and one name “no”
  When user clicks “yes”, the color fill of the shape changes to blue (and the “no” shape stays white). If user clicks “no” , the “no” shape turns blue, and “yes” goes white. It also populates and answer in A1 in this example.</p>
  
  <p>I use the following code which works fine (although im sure could be cut down somewhat) however the problem comes when I need to replicate this code multiple times. For example, if I have a question that has multiple answers like Quantity (answers could be 1 or 2 or 3 or 4 or 5)  then each macro (ie for button “1” ) needs and “active” piece of coder, and a “non active” piece to designate colours to the active shape and all the other non active shapes. This is very repetitive and the code quickly becomes verbose.
  Im hoping there is a way to keep the formatting (fill color, text color etc) in a separate macro such as “Sub Active” and “Sub Non_Active” rather than having to repeat it time after time.  I’ve tried to use  “Call” to grab the macro containing the formatting (like Call Active) but keep getting an error. </p>
  
  <pre><code>Sub yes_button()
  
  'active
  ActiveSheet.Shapes("yes").Select
  ActiveSheet.Shapes("yes").Fill.ForeColor.RGB = RGB(85, 142, 213)                          ' fill: dark blue color
  ActiveSheet.Shapes("yes").Line.BackColor.RGB = RGB(198, 217, 241)                        ' border: light blue color
  ActiveSheet.Shapes("yes").TextFrame.Characters.Font.Color = RGB(255, 255, 255)       '         text: white color
  Range("A1").Formula = "YES" ' fills cell with button value
  
  ' nonactive
  ActiveSheet.Shapes("no").Select
  ActiveSheet.Shapes("no").Fill.ForeColor.RGB = RGB(255, 255, 255)                  '     fill: light blue color
  ActiveSheet.Shapes("no").Line.BackColor.RGB = RGB(198, 217, 241)                  ' border: light blue color
  ActiveSheet.Shapes("no").TextFrame.Characters.Font.Color = RGB(85, 142, 213)     ' text: dark blue color
  
  
  
  End Sub
  
  Sub no_button()
  
  'active
  ActiveSheet.Shapes("no").Select
  ActiveSheet.Shapes("no").Fill.ForeColor.RGB = RGB(85, 142, 213)                       '     fill: dark blue color
  ActiveSheet.Shapes("no").Line.BackColor.RGB = RGB(198, 217, 241)                      '    border: light blue color
  ActiveSheet.Shapes("no").TextFrame.Characters.Font.Color = RGB(255, 255, 255)       ' text: white color
  Range("A1").Formula = "NO" ' fill scell with button value
  ' nonactive
  
  ActiveSheet.Shapes("yes").Select
  ActiveSheet.Shapes("yes").Fill.ForeColor.RGB = RGB(255, 255, 255)                  ' fill: light blue color
  ActiveSheet.Shapes("yes").Line.BackColor.RGB = RGB(198, 217, 241)                  '     border: light blue color
  ActiveSheet.Shapes("yes").TextFrame.Characters.Font.Color = RGB(85, 142, 213)     '     text: dark blue color
  
  End Sub
  </code></pre>
  
  <p>Would appreciate any suggestions. 
  Thankyou</p>