Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Move text in cells via Checkbox tick
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Move text in cells via Checkbox tick

    Hi I can anyone help please
    i have 2 worksheet, worksheet 1 has all patients names in column A2 - A22 and address in B2 -B22. In column C I have a checkbox alongside each name/address. What I want to do is tick the checkbox and the name and address of the person on that row will move to worksheet 2 and and move the names up on sheet 1 so there are no blanks. And if i tick another checkbox another that name will drop under the other name on sheet 2

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    I think it would be a lot easier for you to double click on a cell to perform the task your asking for

    If you want to use a Checkbox then you would have to create 20 Checkboxes and assign code to those Check Boxes

    My solution below does not require any Check Boxes and only about 10 lines of code.

    My solution requires you to Double Click on the Name in Column A of Sheet(1) and then that name in column A and address in column B will be copied over to Sheet(2)

    And the cells in Column A and B of Sheet(1) will be deleted.

    I'm assuming you do not want the entire row deleted.

    So try using this script and see if this will work for you.

    If not then I will need to send you some more complicated code which may do what you want.
    The other way would be to have a script install all the check Boxes and assign code to the checkboxes.

    Now my code will work on Sheet(1) column A starting in row(2) and will work as far down column A as you want.

    Just double click on any cell in Column A of Sheet(1) starting in row(2)

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the Sheet(1) tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window


    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified  6/17/2019  1:42:10 PM  EDT
    If Target.Column = 1 And Target.Row > 1 Then
    Cancel = True
    Dim Lastrow As Long
    Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Target.Resize(, 2).Copy Sheets(2).Cells(Lastrow, 1)
    Target.Resize(, 2).Delete
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    Hi thank you so much for this
    i have tried it out today and it works great. The only change that I would like to do is rather than double click on a cell and that cell moves to sheet 2. Double click on a cell in say column E and by doing this it will take a range of cells and move then to sheet 2. Eg double click cell E4 and that will move range A4:D4 is this possible
    thank you again

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    Try this:
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified  6/18/2019  2:29:06 PM  EDT
    If Target.Column = 5 And Target.Row > 1 Then
    Cancel = True
    Dim Lastrow As Long
    Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Target.Offset(, -4).Resize(, 4).Copy Sheets(2).Cells(Lastrow, 1)
    End If
    End Sub
    But this code deletes nothing.

    You did not say what in this case you want to delete.

    Do you want to delete the entire row from Sheet(1) ?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    Yes sorry I do want to delete. Also is it possible to have a user form pop up with say 4 text boxes on that when those cells move from sheet 1 to sheet 2 the user can fill in the text boxes and those filled in text boxes drop onto the same row but into next columns along. Eg
    A. B. C. D. E. F. G. H. I
    cell moved from sheet 1. Text boxes filled and drop here

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    Forgot to say not to delete the entire row just a range say the first 4 columns ABCD

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    So now your dealing with a lot more.

    So you want the previous script I just provided and now want more added to the same script.

    So after the script I provided now you want a UserForm to Popup with four textboxes.

    And then when you press a button on the UserForm.

    You want those four textbox values to be entered on what sheet and where on the sheet.

    Have you ever created a UserForm and know how to add code to it. Open the UserForm and on and on?

    And why do you think you need to do things this way?

    Tell me your ultimate Goal here. Like wanting a Checkbox earlier I provided a easier way in my Opinion.

    So tell me the Ultimate Goal here and maybe I can provide another easier way.

    If you have no knowledge of UserForms it may be hard for you to create one and do what you want.

    Unless of course your a fast learner. On this forum we never know what the Users capabilities are until we ask.


    Explaining on this forum step by step instructions on how to build a UserForm would be difficult.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    This script now included the delete portion:
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified  6/18/2019  3:09 PM  EDT
    If Target.Column = 5 And Target.Row > 1 Then
    Cancel = True
    Dim Lastrow As Long
    Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Target.Offset(, -4).Resize(, 4).Copy Sheets(2).Cells(Lastrow, 1)
    Target.Offset(, -4).Resize(, 4).Delete
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #9
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    Hi thank you again for spending time on this . my ultimate goal is to have a list of patients on one sheet that are in the ward. And when the leave the ward all their records like name address age male or female that are in columns ABCD are moved to sheet2, which is my leave sheet, by as you said double clicking on column E along side their row where their data is store. Then when it drops into sheet 2 under the previous data that is stored I would also like to have a userform pop up by pressing a button that has text fields and a calendar that will allow the user to say what date that person leaves and what time they leave by filling in the text boxes and pressing on the calendar. This user form data obviously must drop in under columns FG and H(date, time in time our). That’s the goal. What do you think. Thank you again

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,478
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Move text in cells via Checkbox tick

    I asked you several questions in Post 7 which you never answered.

    Like have you ever built a UserForm etc. etc.

    So until you tell me the Name of the Textbox's and where you want each Textbox value to go to it would be hard for me to help you.

    And like you mentioned a calendar in your UserForm. Do you have a DatePicker on your Userform and if so what is it's name

    I have never used a DatePicker which is a UserForm tool that I have never used.

    I believe it would be something like Sheets(2).cells(3,3).value= DatePicker1.value.

    But it's hard to help with no specific details like that.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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