Copy paste Checked box text to next sheet [SOLVED]

[mani_bbc05]

3 things


1. If I will click on “uncheck all” button, then all check boxes should get unchecked.

2. If I will click on “Export Selection to Next Sheet” button then the selected items in column D only should be copied and pasted to next sheet in cell C3 Without any empty rows in between.

3. If we click on check boxes in column B, then all the check boxes under that set should get checked or unchecked (Like if check box in cell B2 is checked/unchecked then all the check boxes in range C3:C6 should be checked or unchecked).


Below is the link for the sample file. It would be highly helpful if you can add the code to the below workbook.


https://www.dropbox.com/s/8kkab9qv4e3bkc2/Copy%20paste%20Checked%20box%20to%20next%20sheet.xlsm

 

[mani_bbc05]

Hi friends, any help for my above problem...

 

[SirJB7]

Hi, mani_bbc05!


Are you hurried? If so start reading the main green sticky post at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

... if you had done so, you should have noticed this points:


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


So posting again in the same topic without adding any useful information, doesn't guarantee at all that bumping it will accelerate any answer.


BTW, you've wrote six posts and you still haven't passed by the Introduce Yourself topic, which is nor a requisite not necessary but just a polite attitude.


Regards!

 

[SirJB7]

Hi, mani_bbc05!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Copy%20paste%20Checked%20box%20to%20next%20sheet%20%28for%20mani_bbc05%20at%20chandoo.org%29.xlsm


VBA code:

-----

[pre]

Option Explicit

' constants
Const gksPattern = "chk"
Const gksAsterisk = "*"

Private Sub cmdUncheckAll_Click()
ChkLike "", False
End Sub

Private Sub chk01_Click()
ChkLike "01"
End Sub

Private Sub chk02_Click()
ChkLike "02"
End Sub

Private Sub chk03_Click()
ChkLike "03"
End Sub

Private Sub ChkLike(psChk As String, Optional pvValue As Variant)
' constants
' declarations
Dim I As Integer, sPattern As String, bValue As Boolean
' start
sPattern = gksPattern & psChk & gksAsterisk
If IsMissing(pvValue) Then
bValue = ActiveSheet.OLEObjects(gksPattern & psChk).Object.Value
Else
bValue = CBool(pvValue)
End If
' process
With ActiveSheet
For I = 1 To .OLEObjects.Count
With .OLEObjects(I)
If .Name Like sPattern Then .Object.Value = bValue
End With
Next I
End With
' end
End Sub

Private Sub cmdExportSelection_Click()
' constants
Const kiSourceColumn = 6
Const ksTargetWS = "Sheet2"
Const kiTargetRow = 3
Const kiTargetColumn = 3
' declarations
Dim I As Integer, J As Integer, sPattern As String
' start
sPattern = gksPattern & gksAsterisk
J = kiTargetRow - 1
With Worksheets(ksTargetWS)
.Range(.Cells(kiTargetRow, kiTargetColumn), _
.Cells(.Rows.Count, kiTargetColumn)).ClearContents
End With
' process
With ActiveSheet
For I = 1 To .OLEObjects.Count
With .OLEObjects(I)
If .Object.Value Then
If .TopLeftCell.Column = kiSourceColumn Then
J = J + 1
Worksheets(ksTargetWS).Cells(J, kiTargetColumn).Value = _
.Object.Caption
End If
End If
End With
Next I
End With
' end
End Sub

[/pre]
-----


Use ActiveX checkbox controls on columns E:F instead of Form checkbox controls on columns B:C. Same for command button controls.


Regards!

 

[mani_bbc05]

Hey Thanks a lot SirJB7,


It is exactly how i was looking for.


But SirJB7 i am not able to move the checkboxes (I am not able to do any modifications in the sheet). Do i need to enable something or the file is password protected?


If so then can i ask you the password for it, as i have do some changes in the file.


Thanks a lot for your help

 

[Debraj]

Hi mani_bbc05..


* Go to Developer Tab.. (I know you have.. as you have already inserted Form Check Box)

* Click "Design Mode" > Now try edit Text for any ChekBox / Command Button.. by all known tricks..

* When done editing.. > again click "Design Mode" ..


If problem not solved.. let the master to finish his Meditation.. he will be appear in front of you..


Regards,

Deb

 

[mani_bbc05]

Hi Debraj,


Thanks a lot for your quick guide, its working now.


Regards,

Mani

 

[SirJB7]

Hi, mani_bbc05!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted. but not hurried.

Regards!


@Debraj Roy

Hi, my friend!

Been meditating the whole day, national flag day so didn't work.

Regards!