Can't figure out how to make the ActiveCell the same cell a Radio Button is in

[DLB20720]

I previously had my worksheet set up with 80 Radio Buttons. My Radio Buttons are in the same cell as the target data. I knew using 'Offset' in the macro to copy and paste the data (my data is dynamic) in the cell would be better, but I couldn't figure out how to do that so I used a specific cell reference (eg; C35) and when I selected the Radio Button in C35 it copied and pasted the data to the destination cell perfectly. But during the week i decided to alter my rows and now the corresponding code no longer aligns with the moved Radio Buttons and target cell. So now I'm back to trying to figure out the correct Offset code and I'm close, but still can't get it to work.

Right now I have my data and the Radio Button in the same cell and this is the first part of the macro;

Private Sub OptionButton1_Click()
Dim OB As Variant
OB = ActiveCell.Offset(,0).Select

The rest of the code works (copies data in cell and pastes in destination cell). The only way I can get this code to work is to first select cell C35 then select the Radio Button. What i want is to just select the Radio Button and have it select the data in the same cell C35), but I can't figure out how to write the code to do this. the code right now makes the 'ActiveCell' the last cell I had selected. I think I've tried 20 different ways to do this and can't figure out what I'm doing wrong. Most VBA goes way over my head so please excuse my ignorance. Hopefully someone here knows what code I can use to grab the data in the same cell as the radio Button. Thanks to anyone that can help.

 

[royUK]

What exactly are you doing?

You could a cell as a Option style button or checkbox

Not CheckBoxes Example

This method would make it simple to find the offset

 

[DLB20720]

Thanks. I should have mentioned I am using ActiveX radio buttons. I tried to open the file from your link but couldn't do it. It looked like you were using it on the other end and would not give me access.

I'm not sure if there is any advantage using a checkbox but I have the feeling I'd still have the same issue with the Offset.

To tell you a bit more about what I have;

I have a worksheet with a live stock market feed for multiple parameters. Based on the live input, I have several equations that 'watch' a list of 2,000 stocks and pull the best stocks (stock symbols) to the top of a list. I actually have 4 lists based on 4 equations. So in each list are the top 20 stocks meeting the various equation criteria. Because the stock prices change constantly, the stock symbols move up and down the list dynamically. If I see a stock I am interested in, I want a method to select that stock symbol, copy the symbol and paste it into cell C35. I have 20 cells to the right of C35 that once a symbol is pasted there, the other cells use INDEX/MATCH to pull up addition metrics for that stock. I tried other methods to grab this dynamic data but all failed. Because it is dynamic, the only way I could accomplish this was to 'copy and paste'. That way when the data in the cell with the radio Button changes, the data in cell C35 remains static.

I have this half way there I just can't figure out what code to use so that when I click on a Radio Button, the macro makes that same cell the ActiveCell.

 

[royUK]

The link is fine now.

My example does not use any controls merely makes a cell act as a check box. So if the cell is in Column A and the stock symbol is in B you just click the "check box" and run some code like

ActiveCell.Offset(,1).Copy Range("AA2")

 

[DLB20720]

I resolved this and wanted to post my results in the event someone is searching for a solution to a similar problem in the future.

After I found the code that allows me to select the data that resides in the same cell as the Radio Button, I found another problem with my code; the specific cell reference I used for the target cell would also change if I added lines to my workbook so I named the target cell (to OBTarget) and changed the destination range accordingly in the code. This will allow me to add or delete rows to my sheet without having to re-write any code.

Private Sub OptionButton1_Click()
OptionButton1.TopLeftCell.Offset(,0).Select
Selection.Copy
Range("OBTarget").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks_
:=False, Transpose:=False
Application.CutCopyMode=False
End Sub

 

[DLB20720]

Roy, thanks again. I didn't see your 2nd post until after I made my previous post, but the code I have now works as I need it to. But I looked at your code and although I didn't try to insert that in my macro, I'm not sure what the Range("AA2") refers to. I'm going to try it later though as from what you are describing, it makes a cell act as a check box without actually being a check box. If that's the case, that's something I'm sure I can use elsewhere on my worksheet.

I appreciate your help!!

 

[Warship]

Not sure I understand but does this help?

Private Sub OptionButton1_Click()
    Dim c As Integer, r As Integer
    With OptionButton1.TopLeftCell
        c = .Column
        r = .Row
    End With
    MsgBox "Column: " & c & " | Row: " & r
End Sub

oops... late to the party as usual...

 

[royUK]

ActiveX controls can be problematic, especially with 80, so it is better to use something like I posted.

The cell reference is just a random cell to demonstrate what code you need to copy the relevant cell.

Your code that you just posted can be simplified

Private Sub OptionButton1_Click()
Range("OBTarget").Value=OptionButton1.TopLeftCell.Offset(,0).value
End Sub

If there are several rows to copy I would copy them in one move use a button that checks what needs copying. I have made a small change to the earlier workbook - on the Marlett sheet if you click a cell in A the cell next to it is copied to a Named Range - OBTarget

New Example

 

[DLB20720]

Roy, Warship, thanks for your responses.

Roy, that code you posted last looks good. I've already gone through and written the code for all 80 buttons. I just copied and pasted the code changing the OB number as I went. Not too bad. I expect I will be making changes to my worksheet in the future and now I can do so without re-writing code.

I forgot to mention; I tried using regular controls but found that sometimes I would accidentally grab and move them. As long as I'm not in Design Mode, that can't happen with ActiveX and that's the primary reason I went with them.

 

[royUK]

You've missed the point - I suggested not using Controls at all.