Annoying problems :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Annoying problems
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

RJC
Board Master


Joined: 29 Jul 2003
Posts: 142

Flag: Australia

Status: Offline

 Reply with quote  

Annoying problems

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

Post Sat Sep 20, 2003 10:46 am 
 View user's profile Send private message

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Annoying problems

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
_________________
Richie

Post Sat Sep 20, 2003 1:39 pm 
 View user's profile Send private message

RJC
Board Master


Joined: 29 Jul 2003
Posts: 142

Flag: Australia

Status: Offline

 Reply with quote  

Re: Annoying problems

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

Post Sun Sep 21, 2003 7:03 am 
 View user's profile Send private message

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Annoying problems

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
_________________
Richie

Post Sun Sep 21, 2003 10:37 am 
 View user's profile Send private message

RJC
Board Master


Joined: 29 Jul 2003
Posts: 142

Flag: Australia

Status: Offline

 Reply with quote  

Re: Annoying problems

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

Post Sun Sep 21, 2003 11:28 am 
 View user's profile Send private message

RJC
Board Master


Joined: 29 Jul 2003
Posts: 142

Flag: Australia

Status: Offline

 Reply with quote  

Re: Annoying problems

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

Post Sun Sep 21, 2003 11:50 am 
 View user's profile Send private message

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: Annoying problems

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 icon_wink.gif ):

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
_________________
Richie

Post Sun Sep 21, 2003 2:18 pm 
 View user's profile Send private message

RJC
Board Master


Joined: 29 Jul 2003
Posts: 142

Flag: Australia

Status: Offline

 Reply with quote  

Re: Annoying problems

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

Post Sun Sep 21, 2003 3:48 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.