+ Reply to Thread
Results 1 to 10 of 10

Help with checkboxes and buttons!

Hybrid View

  1. #1
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Help with checkboxes and buttons!

    Hi everyone I am new to programming and need some help with this problem.
    This is what I need to do.
    I have two sheets. "Sheet1" & "Sheet2"
    On sheet1 I have a header that goes on every page rows 1 to 6.
    On column A I have checkboxes.
    I have data filled in from B7:Z7 from row 7 to 200.
    What I want to do is go down the list and check all the checkboxes that needs to move to Sheet2.
    Once I am done checking the boxes I want to be able to click a button that I create and move the checkboxes that are selected to the next available row on Sheet2. Then clear the data on sheet1 that was moved. So I can enter new data.
    Any help would be greatly appreciated.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Help with checkboxes and buttons!

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with checkboxes and buttons!

    What I want to do is when I click the button take all the rows that have been checked and move them into sheet2. Then clear the rows on sheet1 that have been moved into sheet2.
    Example Book.xlsx

    I hope I did this right. I appreciate any help that I can get. Thanks!

  4. #4
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with checkboxes and buttons!

    I appreciate the help I haven't tried mehmetcik yet. I tried nilem's first. It seemed to work somewhat. I only tried it in the sample book I sent.
    This is my only concern. I only sent you part of the book. You see there are 4 sheets in one sheet if that makes any sense. there are 4 sheets all similar but are made for different parts. Each part has the exact same layout but have to be separate. Which is why I gave a range of columns and rows.
    So the first sheet goes from A7:AA7 and does not stop with rows but I put 200 and Column A has the Check Boxes for the Range B7:AA7. The Second sheet goes from
    AB7:BB7 and so on. Each sheet has 26 columns not including the 1st column that is check boxes. I didn't think this information was important but now that I see the code it very well might be. I do apologize and hope that someone can help me. Thanks!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with checkboxes and buttons!

    with that being said the expired sheet2 well also have the same 4 sheets running across the same way and I want to be able to move the rows into each place on the expires sheet the same way. I didn't want to take up too much space so kept it to the first page. Sorry about that. I really hope that I can get some help. Thank you all!

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Help with checkboxes and buttons!

    Hi scottyb1977,
    try this (in Sheet1 module)
    Sub ertert()
    Dim chb As CheckBox
    For Each chb In Me.CheckBoxes
        If chb.Value = 1 Then
            With chb.TopLeftCell(1, 2).Resize(, 26)
                Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2, 1).Resize(, 26).Value = .Value
                .ClearContents
            End With
        End If
    Next chb
    End Sub

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Help with checkboxes and buttons!

    Ok Try This

    I created a few macros for you.

    Have a play.


    
    
    Public LR As Integer
    Sub CheckboxesCreate()
    
    LastRow ("Sheet1")
    CheckboxesRemove
    For Each c In Range("a7:a" & LR)
        Set X = ActiveSheet.CheckBoxes.Add(c.Left, c.Top, , c.Height)
        X.Characters.Text = ""
        X.Top = c.Top + (c.Height - X.Height) / 2
        X.Left = c.Left + 4 + (c.Width - X.Width) / 2
    '    X.LinkedCell = c.Offset(0, 5).Address
        X.Name = Replace(c.Address(0, 0), "$", "")
    Next
    End Sub
    Sub CheckboxesList()
    On Error Resume Next
    LastRow ("Sheet1")
    For Count = 7 To LR
    If ActiveSheet.CheckBoxes("A" & Count).Value = 1 Then
    Cells(Count, 7).Value = "True"
    Else
    Cells(Count, 7).Value = "False"
    End If
    Next
    On Error GoTo 0
    End Sub
    Sub CheckboxesSetAll()
    On Error Resume Next
        ActiveSheet.CheckBoxes.Value = True
    On Error GoTo 0
    End Sub
    Sub CheckboxesClearAll()
    On Error Resume Next
        ActiveSheet.CheckBoxes.Value = False
    On Error GoTo 0
    End Sub
    
    Sub CheckboxesRemove()
        ActiveSheet.CheckBoxes.Delete
    End Sub
    Sub LastRow(S As String)
    LR = Sheets(S).Cells(Rows.Count, 2).End(xlUp).Row
    10  If Sheets(S).Cells(LR, 2) = "" Then LR = LR - 1: GoTo 10
    End Sub
    
    Sub MoveRows()
    Dim MoveRange As Range
    On Error Resume Next
    LastRow ("Sheet1")
    For Count = 7 To LR
    
    If ActiveSheet.CheckBoxes("A" & Count).Value = 1 Then
    
    If Flag = False Then
    Flag = True: Set MoveRange = Range(Cells(Count, 2), Cells(Count, 27))
    Else
    Set MoveRange = Union(MoveRange, Range(Cells(Count, 2), Cells(Count, 27)))
    End If
    
    ActiveSheet.CheckBoxes("A" & Count).Delete
    End If
    
    Next
    LastRow ("Sheet2")
    
    MoveRange.Copy Destination:=Sheets("Sheet2").Cells(LR + 1, 1)
    
    MoveRange.EntireRow.Delete
    
    On Error GoTo 0
    End Sub
    Attached Files Attached Files
    Last edited by mehmetcik; 02-10-2016 at 02:25 PM.

  8. #8
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with checkboxes and buttons!

    nilem

    Your solution seems to work. There are only two things that concern me. 1. Is there a way to not clear the formula in the cells. 2. When it moves to Sheet2 If there isn't anything on the sheet it doesn't move it or at least I cant find where it moves. Now if I insert a dummy information in the first row then it works perfectly. Any solutions to this would be greatly appreciated. Thanks!

  9. #9
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Help with checkboxes and buttons!

    try this
    Sub ertert()
    Dim chb As CheckBox
    On Error Resume Next
    For Each chb In Me.CheckBoxes
        If chb.Value = 1 Then
            With chb.TopLeftCell(1, 2).Resize(, 26)
                Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2, 1).Resize(, 26).Value = .Value
                .SpecialCells(2).ClearContents
            End With
        End If
    Next chb
    End Sub

  10. #10
    Registered User
    Join Date
    06-16-2015
    Location
    Louisiana
    MS-Off Ver
    2010
    Posts
    12

    Re: Help with checkboxes and buttons!

    Thanks for the help. I appreciate it and it works great. Is there a way instead of clearing the data to shift the cells up to input new data at the bottom. It doesn't make sense to enter new data in between old data. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Radio buttons and checkboxes
    By rhemy1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2015, 08:31 PM
  2. [SOLVED] My buttons/checkboxes keep moving!
    By Brumbot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-07-2013, 09:03 AM
  3. CheckBoxes vs buttons !!!
    By niceguy21 in forum Excel General
    Replies: 3
    Last Post: 11-03-2012, 11:47 AM
  4. CheckBoxes and Buttons
    By Rodrigo Rocha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2010, 09:53 AM
  5. Query regarding Radio buttons & checkboxes
    By kenjoor in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2008, 03:30 PM
  6. Referencing Buttons & Checkboxes
    By bobboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2007, 11:17 AM
  7. Checkboxes vs. Option Buttons
    By JW_4222 in forum Excel General
    Replies: 2
    Last Post: 02-01-2006, 09:40 AM

Tags for this Thread

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