+ Reply to Thread
Results 1 to 7 of 7

appending data based on form checkbox status true or false from one sheet to another sheet

  1. #1
    Registered User
    Join Date
    01-03-2017
    Location
    USA
    MS-Off Ver
    Version 10
    Posts
    11

    appending data based on form checkbox status true or false from one sheet to another sheet

    I have the following code which i cycle through a bunch of checkboxes and if checkbox is true the row does not hide. When false it is hidden.

    Dim c As Range
    For Each c In Worksheets("Sheet1").Range("G6:G80")
    If c.Value = "True" Then
    c.EntireRow.Hidden = False
    Else
    c.EntireRow.Hidden = True
    End If
    Next c

    The following is what i want to do. If the checkbox is true i also want the information from the cell to be copied into another sheet for example sheet2. If there is multiple checkboxes i want all the information to be copied into a cell from ecah checkbox selected and appended to each other. For example if i have 1234 in sheet 1 column/row G6 and then 5678 in column/row G7 i want the sheet 2 output to show all info in one cell for example Sheet 2 i would like to display the info of sheet one 1234 carriage return then 5678 carriage return ect.

    any help would be appreciated

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: appending data based on form checkbox status true or false from one sheet to another s

    Your code indicates you have True\False in G6:G80 but your request describes that there is text in those cells. Confusing.

    This loops through G6:G80 and gets the text from column H .Offset(,1) Change the offset to suit
    It puts the text in cell A1 on sheet 2

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    01-03-2017
    Location
    USA
    MS-Off Ver
    Version 10
    Posts
    11

    Re: appending data based on form checkbox status true or false from one sheet to another s

    Thank you for you help this is much appreciated I trying to teach my self how to do this so your example is very helpful.

    you are correct in the G column I have the true and false status displayed based on the checkbox being checked or not. I am trying to follow your code. Sorry im new to VB. Basically if the value in G6 and G80 is true I want the information in column B sheet 1 to be copied to sheet 2. Lets say for example the info should be moved to sheet 2 cell A1. The issue is if I have more than one true condition for example G6 is true and G7 is true G8 is true that means now I have corresponding cells B6 , B7 and B8 with information that I need to copy to sheet 2 position A1. But if the info is moved I need there to be a carriage return between each item for example if the info is moved 1234,5678,9101112 I need it to be displayed as
    1234
    5678
    9101112

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: appending data based on form checkbox status true or false from one sheet to another s

    Just change the offset from 1 to -5 to get the values from column B if column G is True

    c.Offset(, -5)

    The code already does the rest of what you described.

  5. #5
    Registered User
    Join Date
    01-03-2017
    Location
    USA
    MS-Off Ver
    Version 10
    Posts
    11

    Re: appending data based on form checkbox status true or false from one sheet to another s

    Ok it looks like its close it works the way i need. The only thing when i go to sheet 2 it repeats the text a number of 8 times for some reason in the sheet 2 A1 cell. I will see if i can figure out why but i appreciate your help unless you know why this is happening. i dont want to take up to much of your time.

    I cant thank you enough for this info so far.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: appending data based on form checkbox status true or false from one sheet to another s

    I don't know why it puts the text 8 times.

    Do you have any other code?
    Where did you put the code I suggested?
    Did you change anything else besides the offset?

  7. #7
    Registered User
    Join Date
    01-03-2017
    Location
    USA
    MS-Off Ver
    Version 10
    Posts
    11

    Re: appending data based on form checkbox status true or false from one sheet to another s

    It works great . The replication issue was my fault i had a bug in my code that is now resolved. Everything appears to be working correctly thanks for your help.

+ 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. Changing checkbox background color - loop to check all tick boxes on sheet for status TRUE
    By Vegiepie2016 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-19-2016, 10:33 AM
  2. ActiveX CheckBox True or false send value to other sheet cell value and add data
    By PatrikOslo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2016, 11:04 AM
  3. [SOLVED] Numbers based on True or False in a differnt sheet
    By cantona_lives in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-29-2014, 02:31 AM
  4. [SOLVED] Use checkbox on sheet ('true' or 'false') to decide if run macro
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2013, 09:12 PM
  5. Sum values based on TRUE/FALSE property on different sheet?
    By grantpanderson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-19-2010, 11:38 AM
  6. Filling in a cell based on Checkbox true/false
    By Rivalwon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2007, 07:07 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