Problem with Option Buttons in Visual Basic

Problem with Option Buttons in Visual Basic

Didn't find your answer?

I am just starting out using Visual Basic (in Excel) and am stuck at the first hurdle!

I've opened up a spreadsheet with a set of six ActiveX Option Buttons. In cells E203 to E208 I have some values (I originally put these on another sheet in the workbook, but couldn't get the macro to recognise the destination).

In cell E100 I want the answer to appear.

My second problem was I coudn't get the button to pick up the values in cells E203 etc either, so I reverted to putting the actual values into the macro, which now reads:

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then Range("e100").Value = 140
End Sub

and so on, with 140 for button 1, 200 for button 2 etc.

My main problem is on the first selection of the Option Button the value goes into E100, but if I then select a different button the value doesn't change.

The buttons work as a set though with only one button blanked out at once.

What is wrong with the macro please?

The next stage, after I get this to work, will be to create other sets of Option Buttons. I suspect that I will encounter problems creating new sets on the same worksheet - should I be learning how to use Userforms before I go any further?

I'm using Excel in Office 2010.

I've been using information from www.excel-VBA-easy.com as my learning source, and have got Excel VBA Programming fro Dummies on order. Can anyone recommend another good source for learning VBA please?

Thanks for any or all advice.

Replies (2)

Please login or register to join the discussion.

avatar
By ACDWebb
09th May 2011 09:41

Select ALL your Option buttons

Then set the Cell Link property to a particular cell.

Having done that the Cell you have set will return 1 to 6 depending which of your option buttons is selected. You can use the entry in your Cell Link cell to determine which number to use

=IF(CellLink = 1,140,IF(CellLink = 2,200,etc))

Or rater in the macro use

Select Case Range(Cell Link)

 Case 1

  140

 Case 2

  200

 Case 3

  ???

 Case 4

  ???

 Case 5

  ???

 Case Else

  ???

End Select

Thanks (0)
avatar
By Richard Willis
09th May 2011 12:40

My 'Bible' is
'VBA for Excel Made simple' by Keith Darlington. I have recommended this range of books several times on Aweb havinbg started with web pages. ISBN 0-7506-6097-X

Thanks (0)