Annoying problems

RJC · Sep 20, 2003

Two questions that are driving me mad. Could anyone help.

First
I have checkboxes on my spreadsheet. How do I clear the ticked boxes when I clear my spreadsheet. At the moment they just go into a greyed out tick. I want then to be clear.

Second
In T75:T100 I have cells formatted as general and they contain text. The cells that contain the text vary depending on the workings of my spreadsheet.
I want to get just the range that contains data to paste to AF76. I don't want any cells in the range that are empty. How do I do this?

Please!
Regards

Richie(UK) · Sep 20, 2003

Hi,

One: Could you clarify - how are you clearing your sheet ? do you want the tick removed or the checkbox ?

Two: Try using the SpecialCells method to isolate the data that you want. Like this perhaps:

Code:

Sub Test()
    Dim rngTest As Range
    
    With Sheet1
        Set rngTest = .Range("T75:T100").SpecialCells(xlCellTypeConstants, xlTextValues)
        rngTest.Copy Destination:=.Range("AF76")
    End With
    
End Sub

HTH

RJC · Sep 21, 2003

Hi Richie,
Thanks for your reply.
I want the tick only removed so that all I see is a BLACK tick if selected or an empty white box if not. The grey tick is confusing.
I'm clearing my sheet by just using the normal clear contents option of the linked cells containing True or False. When I do this all the boxes display a greyed out tick. I was expecting just a clear box that I could then just tick to select. (Like the boxes on this page.

Is it possible to do this.
Regards
Rick

Richie(UK) · Sep 21, 2003

Hi Rick,

It sounds as if you have Linked Cells with the CheckBoxes, eg if linked to A1 then a 0 or False will clear the box, <>0 or True will tick the box, and clearing the cell (as you are doing) will give the grey effect.

You can manually delete the links or try something like this:

Code:

Sub ClearCBs()
    Dim obj As Object
    For Each obj In Worksheets("Sheet1").OLEObjects
        If TypeName(obj.Object) = "CheckBox" Then
            obj.Object.Value = False
            obj.LinkedCell = ""
        End If
    Next
End Sub

HTH

RJC · Sep 21, 2003

Hi Richie,
Thanks a lot for your prompt reply. That was invaluable information and was exactly what I had i.e. my boxes were linked to cells in columnAC. I could simple include in my "clear" macro, code to just add a O or False to the AC cells. I've tried this and presto, all the check boxes are clear as I wanted. I'll also include your code as a good woraround. Happy days!

Once again, very grateful for your help.
Kind regards,
Rick

RJC · Sep 21, 2003

Hi Richie,
I've just run into a further problem regarding the copying of non blank cells that you responded to. I've changed the code just slightly. Think it's ok, but my problem now is that I wish to copy the range of non blank cells to other area's be they spreadsheet or word documents etc.

What do I need to do to the code to just have it copy to memory(clipboard) so I can paste to say another application. I've tried to comment out the last line where it copies to AF76 and added the Selection.Copy line which you can see I've since commented out. When I do this, the option to Paste is greyed out. Could you help with this please.

Thanks,
Rick

Sub Getdata()

'ActiveSheet.Unprotect
'Application.ScreenUpdating = False

Dim rngTest As Range
With Sheets("MyData")
Set rngTest = .Range("T75:T176").SpecialCells(xlCellTypeConstants, xlTextValues)
' rngTest.Copy Destination:=.Range("AF75")
' Selection.Copy
End With

Richie(UK) · Sep 21, 2003

Hi Rick,

If you leave out the "Destination:=" part of the code then the Copy instruction will copy the range to the Clipboard. (No need to use Selection).

See if the following helps give you an indication of how you could proceed, depending upon your requirements (ie pick one, don't use them ALL :wink: ):

Code:

Sub Test()
    Dim rngTest As Range, wsNew As Worksheet, wbkTarget As Workbook
    Dim appWrd As Word.Application
    
    With Sheet1
        Set rngTest = .Range("T75:T100").SpecialCells(xlCellTypeConstants, xlTextValues)
        
        rngTest.Copy Destination:=.Range("AF76")
        'copy to destination on same sheet

        rngTest.Copy Destination:=Sheet2.Range("A1")
        'copy from Sheet1 to Sheet2

        rngTest.Copy
        Set wsNew = Worksheets.Add
        wsNew.Range("A1").PasteSpecial Paste:=xlValues
        'copy to clipboard and paste to newly added sheet

        rngTest.Copy
        Set wbkTarget = Workbooks("TargetWorkbookName")
        wbkTarget.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlValues
        'copy to clipboard and paste to another workbook
        
        '*** For this next bit go to Tools / References and
        '*** add a reference to Microsoft Word
        rngTest.Copy
        Set appWrd = CreateObject("Word.Application")
        With appWrd
            .Visible = True
            .Documents.Add
            .Selection.Paste
            .ActiveDocument.SaveAs FileName:="AWordFilename.doc"
            .Quit
        End With
        Set appWrd = Nothing
        'copy to clipboard and paste to new Word doc
    End With
    
End Sub

HTH

RJC · Sep 21, 2003

Hi Richie,
Much obliged for your help. You have gone to a great deal of trouble to help me. I certainly appreciate your goodwill.
Cheers!
Rick

Annoying problems

RJC

Active Member

Excel Facts

Richie(UK)

MrExcel MVP

RJC

Active Member

Richie(UK)

MrExcel MVP

RJC

Active Member

RJC

Active Member

Richie(UK)

MrExcel MVP

RJC

Active Member

Similar threads

Forum statistics

Share this page

Annoying problems

Active Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock