Results 1 to 19 of 19

Problem with generating new list

Threaded View

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    24

    Problem with generating new list

    Hi there. Im trying to keep track of completed tasks. I made a workbook where all tasks are listed in sheet1 (Ark1) and I want rows with completed tasks (identified by marked checkboxes) sequently moved to sheet2 (ark2).

    I used this recepie to get med started: https://www.extendoffice.com/documen...ell-value.html. I must admit that I dont understand all lines, so it's somewhat difficult to modify it to suit my needs. Here is my code:
    Sub Commandbutton1_Click()
        Dim xRg As Range
        Dim xCell As Range
        Dim I As Long
        Dim J As Long
        I = Worksheets("Ark1").UsedRange.Rows.Count
        J = Worksheets("Ark2").UsedRange.Rows.Count
        If J = 1 Then
           If Application.WorksheetFunction.CountA(Worksheets("Ark2").UsedRange) = 0 Then J = 0
        End If
        Set xRg = Worksheets("Ark1").Range("H1:H" & I)
        On Error Resume Next
        Application.ScreenUpdating = False
        For Each xCell In xRg
            If xCell = True Then
                xCell.EntireRow.Copy Destination:=Worksheets("Ark2").Range("A" & J + 1)
                Worksheets("Ark2").Range("G" & J + 1).Value = Date + Time
                xCell.EntireRow.Delete
                J = J + 1
            End If
        Next
        Application.EnableEvents = False
        Cells.Range("B2").Value = Date + Time
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Here is a screenshot of my first Sheet1 (Ark1) so you can get an idea what I'm trying to do.
    Sheet1 (ark1)
    sheet1.PNG
    Sheet2 (ark2) is just a copy of the first 3 rows


    However. Using this code gets me into all kind of strange situations, and I cant figure out how to solve it:

    First: I imbedded checkboxes into cells. While the code copies the checkbox from the row correctly, It also copy in checkboxes in the row above the row of interest.
    Second: If I have several checkboxes marked at the same time, the code only moves some rows. If I however just run the code over and over, all the rows of interes are included
    Third When trying to delete rows in sheet2 it does not let med delete the checkboxes in sheet2. I allso figured out that excel had layered several hundered checkboxses into one cell



    BTW I run this code to link my checxboxes:
    Sub LinkCheckBoxes()
    Dim chk As CheckBox
    Dim lCol As Long
    lCol = 2 'rader fra checkboxen
    
    For Each chk In ActiveSheet.CheckBoxes
       With chk
          .LinkedCell = _
             .TopLeftCell.Offset(0, lCol).Address
       End With
    Next chk
    
    End Sub
    Not beeing a world champion coder, I would really appreciate some help figuring out how to solve these problems

    Thanks
    Last edited by Peltz; 04-24-2017 at 05:33 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VLOOKUP Problem!! Not generating names
    By taj1504 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2016, 09:21 AM
  2. [SOLVED] Generating a list with random units per sale from a list with the totals
    By Juliana33 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 11-05-2015, 03:45 AM
  3. [SOLVED] VBA macro problem regarding folder generating
    By istvanvincze90 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-20-2015, 09:39 AM
  4. Formula generating problem
    By macimacileona in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-01-2015, 06:22 PM
  5. Problem while generating vloop in excel using vb 6.0
    By anavarathan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2014, 03:00 PM
  6. macro for automatically generating a list from a list
    By SIFT-MS88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2010, 01:44 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