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

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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
 
Last edited:

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

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

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117

ADVERTISEMENT

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

Board Regular
Joined
Sep 29, 2013
Messages
117
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

Well-known Member
Joined
Jul 17, 2007
Messages
1,051

ADVERTISEMENT

Not sure I understand but does this help?
Code:
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

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
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

Board Regular
Joined
Sep 29, 2013
Messages
117
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.
 
Last edited:

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
You've missed the point - I suggested not using Controls at all.
 

Forum statistics

Threads
1,136,446
Messages
5,675,901
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top