Results 1 to 11 of 11

Option button linked cells

Threaded View

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Option button linked cells

    Hi all bit of a VB novice here and could use some help.

    I've been putting together a budget spreadsheet that allows a user to select what payment method was used for each item, this is done with ActiveX option buttons, these all work no problem.

    The issue arises when a user attempts to put in a new row. I've borrowed a piece of VB code that will insert a new row along with all formulas from above and a new set of option buttons (this solved the first problem I had)

    The trouble is, the new set of option buttons are still considered a part of the same group so do not operate independently of the other groups, they also do not have a linked cell. Is there a way to do this via macro?

    E.g. The first row's option buttons are in cell F12, they are linked with cells J,K and L 12 and are in a group called 'Group1', the next row's option buttons (which I created manually) are in F13 and are similarly linked to J,K and L 13, the group name is 'Group2' If I run the macro below, the new row is created but the new option buttons are all in 'Group2' still and have no linked cell where it would need to be J,K and L 14.

    Basically, is there a macro to set properties of ActiveX option buttons as they are inserted with a new row?

    Here is the code I've borrowed from another site to insert rows:

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

    Sub InsertRowsAndFillFormulas()
    
    
    Dim x As Long
    ActiveCell.EntireRow.Select
    If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
    "How many rows do you want to add?", Title:="Add Rows", _
    Default:=1, Type:=1)
    If vRows = False Then Exit Sub
    End If
    Dim sht As Worksheet, shts() As String, i As Long
    ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
    Windows(1).SelectedSheets.Count)
    i = 0
    For Each sht In _
    Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name
    
    
    x = Sheets(sht.Name).UsedRange.Rows.Count
    
    Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
    Resize(rowsize:=vRows).Insert Shift:=xlDown
    
    
    Selection.AutoFill Selection.Resize( _
    rowsize:=vRows + 1), xlFillDefault
    
    
    On Error Resume Next
    Next
    
    End Sub
    Last edited by 6StringJazzer; 10-17-2014 at 09:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Disable cells contingent on an option button
    By jghender in forum Excel General
    Replies: 0
    Last Post: 12-27-2012, 06:10 PM
  2. Option Button that shows/hide certain cells
    By DoubLeA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 03:31 PM
  3. Radio Button Groups linked to cells for counting values
    By kathyb10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-31-2011, 06:35 PM
  4. [SOLVED] using option button to highlight cells
    By Carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-05-2006, 01:55 PM
  5. Need Help With Linked Option Button
    By Bd_Blues in forum Excel General
    Replies: 0
    Last Post: 02-01-2005, 08:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1