Move text in cells via Checkbox tick

[Malcolm torishi]

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

 

[My Aswer Is This]

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

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

 

[Malcolm torishi]

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

 

[My Aswer Is This]

Try this:

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) ?

 

[Malcolm torishi]

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

 

[Malcolm torishi]

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

 

[My Aswer Is This]

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.

 

[My Aswer Is This]

This script now included the delete portion:

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

 

[Malcolm torishi]

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

 

[My Aswer Is This]

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.