Code to move data forward and back, using a checkbox
I need a very simple code, with a few lines to move data from "D1:K1" to "Q1:X1" and backwards, using a checkbox.
When the checkbox is = True - Move from "D1:K1" to "Q1:X1".
When the checkbox is = False - Move from "Q1:X1" to "D1:K1".
I have a code, but because I use it in a calender with 365 days, it slow the Excel down, when it's repeated 365 times
The code below is repeated one time for each Row.
This code works, but slow the Excel down.
[VBA]Private Sub CheckBox216_Click()
If CheckBox216.Value = True Then
Range("D261:K261").Select
Selection.Copy
Range("Q261").Select
ActiveSheet.Paste
Range("D261:K261").Select
Selection.ClearContents
Range("B261").Select
ElseIf CheckBox216.Value = False Then
Range("Q261:X261").Select
Selection.Copy
Range("D261").Select
ActiveSheet.Paste
Range("Q261:X261").Select
Selection.ClearContents
Range("B261").Select
End If
End Sub
[/VBA]
Can anybody please help me making a more simple code to move my data.
Thank you in advance.
Last edited by Aussiebear; 01-10-2013 at 10:21 PM.
Reason: Added the correct tags to the supplied code
my Firefox displays all your code in one very long line of code
[vba]Private Sub CheckBox216_Click()
If CheckBox216.Value Then
With Range("D261:K261")
Range("Q261:X261").Value = .Value
.ClearContents
End With
Else
With Range("Q261:X261")
Range("D261:K261").Value = .Value
.ClearContents
End With
End If
Range("B261").Select
End Sub[/vba] Ciao,
Holger
[vba]
Private Sub CheckBox1_Click()
If CheckBox1.Value Then
Range("D261:K261").Cut Range("Q261")
Else: Range("Q261:X261").Cut Range("D261")
End If
End Sub[/vba]
What I did was to record a macro and use the macro-code to make the VBA, using If and ElseIf, but using the code 365 times, slowed the Excel down.
Winon.
Your code ("Q261:X261") = ("D261:K26") doesn't insert the data in ("Q261:X261") and leave the cells empty.
It looks right and I don't know what is wrong.
Osvaldo
You code is very simple and I guess it wont slow the Excel down when it's used it 365 times, but I can't use "CUT", because I have some lines in the cells, I need to keep.
Hoger.
Your code works perfectly.
I'll use the code and hope it wont slow the Excel down.
Try to Google search html5test.
There is a new Chrome Canary and it have a good score in the test.
If you use the Tab "Compare", when the site open, you can compare different browsers.
The best Browser, with the higest score right now is Maxthon 3.4.5
Yes the Code shows in one long line, and I do not know why.
Somehow I forgot to add .Value to the Code which in fact should look like this, and it works fine!
[VBA]Private Sub CheckBox216_Click()
If CheckBox216.Value = True Then
Range("Q261:X261") = Range("D261:K261").Value
Range("D261:K261").ClearContents
Range("B261").Select
I'm sorry, but I'm almost new in this VBA coding and is learning by trying.
As I wrote I have a calender with 13 month I can re-use it year after year.
It goes from June 2012 - June 2013 etc.
In the settings for the calender, I can change the first day in June and if the year have the 29 of February.
I need to move data for each day from ("D:K") to ("Q:X") and backwards again.
What I did.
- I made a checkbox.
- Took 31 copies.
- Aligned all the 31 checkboxes to fit in one month.
- Made a group of the 31 checkboxes.
- And copied the group to all the other month. (It's a lot of checkboxes)
Then I went into each checkbox and pasted one of the "real VBA codes" and by using Search&Replace I changed each row number to fit to each checkbox.
I also have 2 x 13 buttons.
- 13 to "goto" a month.
- 13 to "print" an area in a month.
It also have three more Tabs with normal Excel codes, from where it gets some date and in one of the other Tabs, there is the same 2 x 13 buttons.
The Excel work very well, but after inserting all the checkboxes it became very heavy to use.
It e.g. takes 5-10 sec. before it open the print preview.
And when I scroll down it say "Not answering" for 5-10 sec., before responding at the scroll.
When I open the Excel, it also takes 5-10 sec., before all the buttons and checkboxes is loaded and visible.
The size at all the Excel is 1.25 Mb and it should not be a problem.
What did I do wrong and why did the excel became heavy to use (not responding), after inserting all the checkbox-codes ?
I had found out of the problem.
The Excel slow down, because it load 365 checkboxes and a lot of other button's and codes.
Instead I had made some menu's, using UserForm's.
I have a UserForm Menu, which open a specifik month.
A new UserForm with a checkbox for each day in the month open
When I mark the checkbox in a specifik month, I move data from ("D:K") to ("Q:X").
And backwards again from ("Q:X") to ("D:K"), when I unmark the checkbox.
I use this code in each checkbox and it works.
[VBA]Private Sub CheckBox1_Click()
If CheckBox1.Value Then
With Range("D1000:K1000")
Range("Q1000:X1000").Value = .Value
.ClearContents
End With
Else
With Range("Q1000:X1000")
Range("D1000:K1000").Value = .Value
.ClearContents
End With
End If
Range("B1000").Select
End Sub
[/VBA]
I need the selection in the checkbox to be remembered, when the Userform is closed and reopend again.
Can anybody help me.
Thank you in advance.
Ib
Last edited by Aussiebear; 01-10-2013 at 10:22 PM.
Reason: Added the correct tags to the supplied code
I can probably make your code to work, but it copy from ("D:K") to ("Q:X")
I need to move the data and backwards again, when the checkbox is unchecked.
What I really need, is a code to remember the marking in the checkboxes, after the UserForm is closed and reopened again.
Or some other way to remember the marking.
I'm not sure what you're telling, but what I need in this calender, is to move data from the left side ("D:K") to the right side ("Q:X"), when the checkbox is marked.
And when the mark is removed again, move the data back from the right side ("Q:X") to the left side ("D:K").
The code I have works.
But the problem is, when the UserForm is closed and opened again, the marking is gone and when I mark again, I'll move the empty fields from the left side and overwrite the right side with nothing and all the data is gone.
You can doubleclick 1 of the cells you want to be moved.
[vba]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If (Not Intersect(Target, Columns("D:K")) Is Nothing Or Not Intersect(Target, Columns("Q:X")) Is Nothing) And Target.Value <> "" Then
Cells(Target.Row, 17 - IIf(Target.Column > 16, 13, 0)).Resize(, 8) = Cells(Target.Row, 4 + IIf(Target.Column < 17, 0, 13)).Resize(, 8).Value
Cells(Target.Row, 4 + IIf(Target.Column < 17, 0, 13)).Resize(, 8).ClearContents
Cancel = True
End If
End Sub
Hmm. Interesting, but the Excel is not only for me, but for private use, for a lot of colleagues in the company.
To make UserForms-Menu's to navigate in all the functions, makes it more user-friendly.
First I made the checkboxes directly at the sheet and it worked in both directions, depending of the checkox was True or False, but the Excel was not responding or it took a long time load with 13 months, 365 checkboxes and a lot of other buttons, although it only was at 1.25 Mb.
The value was saved in the checkboxes, when the Excel was closed.
Making UserForms-Menu's, I was thinking I didn't needed to load all the Excel at one time, but could load the codes, when they were needed.
Maybe I need to make a Tab for each month and put the 30/31 checkboxes at each Tabs ?
As you probably can hear, I'm new in all this VBA coding and is learning by trying.
what you want you can accomplish with 1 button.
If the cursor is in a cel D:K, it will move to the right, if the cursor is in Q:X it wil move to the left.
Can you help me making a simple code to a CommandButton saying something as:
If D1 is different from empty
Then move D1:K1 to Q1:X1
clearContens D1:K1
ElseIf Q1 is different from empty
Then move Q1:X1 to D1:K1
ClearContens Q1:X1
The code should of course not do anything, if both K1: and Q1: is empty.
-------
With a code like that I can make a UserForm with 30/31 buttons to each month and the user don't need to stay i a specific cell to make the movement of the data.
And if the user move the data in a wrong day, it's easy to move the data back again.
A simple code like that would be perfect for my Excel.
Can anybody please help me with a simple button code to move data from D:K to Q:X.
- If D: is empty, jump to Q:X and move the data.
- If Q: is empty, jump to D:K and move the data.
- If both D: and Q: is empty, do nothing and jump out of the code.
Reply With Quote
