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.
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
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