VBA solution to paste text into megred cells

I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded feature. 
Users are complaining that on trying to paste text into merged cells gives 
Excel Warnings:

   "Data on the Clipboard is not the same size and shape as the selected 
area. Do you want to paste anyway?"

 And:

    "Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in the 
formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
Application warning(s) above and then, once trapped (2) placing the cursor 
in the formula bar and finally (3) Paste the clipboard into the formula bar.



Has anyone any ideas please?



TIA



Phil


0
PPL
3/5/2010 9:01:32 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
1367 Views

Similar Articles

[PageSpeed] 12

Does this could help you ?

Sub CopyToMergedCells()


Set Origin = Range("D1")
Set dest = Range("B1")

dest.Select 'To Select Merged Cells if it is the case

 With Selection
  If .MergeCells = False Then

    Origin.Copy dest

  Else

      .ClearContents
      .MergeCells = False

      Origin.Copy dest

      .MergeCells = True

   End If

  End With

End Sub

Mishell


"PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
Oiekn.69271$Ye4.61706@newsfe11.iad...
>I do realize that merged cells are bad news.
>
> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
> feature. Users are complaining that on trying to paste text into merged 
> cells gives Excel Warnings:
>
>   "Data on the Clipboard is not the same size and shape as the selected 
> area. Do you want to paste anyway?"
>
> And:
>
>    "Cannot change part of a merged cell."
>
>
>
> Interestingly, though if the user hits F2, which places the cursor in the 
> formula bar, then the paste operation works!
>
>
>
> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>
> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
> Application warning(s) above and then, once trapped (2) placing the cursor 
> in the formula bar and finally (3) Paste the clipboard into the formula 
> bar.
>
>
>
> Has anyone any ideas please?
>
>
>
> TIA
>
>
>
> Phil
>
> 


0
Mishell
3/6/2010 3:20:20 AM

To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

 If rngCell.MergeCells Then

  rngCell.MergeCells = False

 End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
Oiekn.69271$Ye4.61706@newsfe11.iad...
>I do realize that merged cells are bad news.
>
> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
> feature. Users are complaining that on trying to paste text into merged 
> cells gives Excel Warnings:
>
>   "Data on the Clipboard is not the same size and shape as the selected 
> area. Do you want to paste anyway?"
>
> And:
>
>    "Cannot change part of a merged cell."
>
>
>
> Interestingly, though if the user hits F2, which places the cursor in the 
> formula bar, then the paste operation works!
>
>
>
> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>
> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
> Application warning(s) above and then, once trapped (2) placing the cursor 
> in the formula bar and finally (3) Paste the clipboard into the formula 
> bar.
>
>
>
> Has anyone any ideas please?
>
>
>
> TIA
>
>
>
> Phil
>
> 


0
Mishell
3/6/2010 7:06:37 AM
Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from 
VBA - that way I should be able to obviate messing with the merged cells?

Phil


"Mishell" <Mishell@nospam.ca> wrote in message 
news:eL8vRuPvKHA.1796@TK2MSFTNGP02.phx.gbl...
>
>
> To UnMerge DestinationArea before Copy.
>
>
> Sub UnMergeDestinationAreaAndPaste()
>
> Dim rngToCheck As Range, rngCell As Range, rngChecked As Range
>
> Set origin = Sheets("Sheet2").Range("G1:I3")
> Set dest = Sheets("Sheet3").Range("B1")
>
> Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)
>
> For Each rngCell In rngToCheck.Cells
>
> If rngCell.MergeCells Then
>
>  rngCell.MergeCells = False
>
> End If
> Next rngCell
>
> origin.Copy dest
>
> End Sub
>
> Mishell
>
>
> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
> Oiekn.69271$Ye4.61706@newsfe11.iad...
>>I do realize that merged cells are bad news.
>>
>> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
>> feature. Users are complaining that on trying to paste text into merged 
>> cells gives Excel Warnings:
>>
>>   "Data on the Clipboard is not the same size and shape as the selected 
>> area. Do you want to paste anyway?"
>>
>> And:
>>
>>    "Cannot change part of a merged cell."
>>
>>
>>
>> Interestingly, though if the user hits F2, which places the cursor in the 
>> formula bar, then the paste operation works!
>>
>>
>>
>> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>>
>> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
>> Application warning(s) above and then, once trapped (2) placing the 
>> cursor in the formula bar and finally (3) Paste the clipboard into the 
>> formula bar.
>>
>>
>>
>> Has anyone any ideas please?
>>
>>
>>
>> TIA
>>
>>
>>
>> Phil
>>
>>
>
> 


0
PPL
3/6/2010 3:50:46 PM
"PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
vRukn.32278$Dv7.29500@newsfe17.iad...
> Thanks Mishell I'll try your ideas and I'll let you know
> Do you have any ideas on how the Formula Bar can accessed directly from 
> VBA - that way I should be able to obviate messing with the merged cells?

You could try to put a STOP statement in your code.

>
> Phil
>
>
> "Mishell" <Mishell@nospam.ca> wrote in message 
> news:eL8vRuPvKHA.1796@TK2MSFTNGP02.phx.gbl...
>>
>>
>> To UnMerge DestinationArea before Copy.
>>
>>
>> Sub UnMergeDestinationAreaAndPaste()
>>
>> Dim rngToCheck As Range, rngCell As Range, rngChecked As Range
>>
>> Set origin = Sheets("Sheet2").Range("G1:I3")
>> Set dest = Sheets("Sheet3").Range("B1")
>>
>> Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)
>>
>> For Each rngCell In rngToCheck.Cells
>>
>> If rngCell.MergeCells Then
>>
>>  rngCell.MergeCells = False
>>
>> End If
>> Next rngCell
>>
>> origin.Copy dest
>>
>> End Sub
>>
>> Mishell
>>
>>
>> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
>> Oiekn.69271$Ye4.61706@newsfe11.iad...
>>>I do realize that merged cells are bad news.
>>>
>>> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
>>> feature. Users are complaining that on trying to paste text into merged 
>>> cells gives Excel Warnings:
>>>
>>>   "Data on the Clipboard is not the same size and shape as the selected 
>>> area. Do you want to paste anyway?"
>>>
>>> And:
>>>
>>>    "Cannot change part of a merged cell."
>>>
>>>
>>>
>>> Interestingly, though if the user hits F2, which places the cursor in 
>>> the formula bar, then the paste operation works!
>>>
>>>
>>>
>>> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>>>
>>> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
>>> Application warning(s) above and then, once trapped (2) placing the 
>>> cursor in the formula bar and finally (3) Paste the clipboard into the 
>>> formula bar.
>>>
>>>
>>>
>>> Has anyone any ideas please?
>>>
>>>
>>>
>>> TIA
>>>
>>>
>>>
>>> Phil
>>>
>>>
>>
>>
>
> 


0
Mishell
3/6/2010 7:10:38 PM
Other than placing the project in Break mode - and displaying the VBA code - 
the STOP statement seems to have no affect.
Am I missing something

"Mishell" <Mishell@nospam.ca> wrote in message 
news:OhQ%231CWvKHA.5008@TK2MSFTNGP05.phx.gbl...
>
> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
> vRukn.32278$Dv7.29500@newsfe17.iad...
>> Thanks Mishell I'll try your ideas and I'll let you know
>> Do you have any ideas on how the Formula Bar can accessed directly from 
>> VBA - that way I should be able to obviate messing with the merged cells?
>
> You could try to put a STOP statement in your code.
>
>>
>> Phil
>>
>>
>> "Mishell" <Mishell@nospam.ca> wrote in message 
>> news:eL8vRuPvKHA.1796@TK2MSFTNGP02.phx.gbl...
>>>
>>>
>>> To UnMerge DestinationArea before Copy.
>>>
>>>
>>> Sub UnMergeDestinationAreaAndPaste()
>>>
>>> Dim rngToCheck As Range, rngCell As Range, rngChecked As Range
>>>
>>> Set origin = Sheets("Sheet2").Range("G1:I3")
>>> Set dest = Sheets("Sheet3").Range("B1")
>>>
>>> Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)
>>>
>>> For Each rngCell In rngToCheck.Cells
>>>
>>> If rngCell.MergeCells Then
>>>
>>>  rngCell.MergeCells = False
>>>
>>> End If
>>> Next rngCell
>>>
>>> origin.Copy dest
>>>
>>> End Sub
>>>
>>> Mishell
>>>
>>>
>>> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
>>> Oiekn.69271$Ye4.61706@newsfe11.iad...
>>>>I do realize that merged cells are bad news.
>>>>
>>>> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
>>>> feature. Users are complaining that on trying to paste text into merged 
>>>> cells gives Excel Warnings:
>>>>
>>>>   "Data on the Clipboard is not the same size and shape as the selected 
>>>> area. Do you want to paste anyway?"
>>>>
>>>> And:
>>>>
>>>>    "Cannot change part of a merged cell."
>>>>
>>>>
>>>>
>>>> Interestingly, though if the user hits F2, which places the cursor in 
>>>> the formula bar, then the paste operation works!
>>>>
>>>>
>>>>
>>>> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>>>>
>>>> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
>>>> Application warning(s) above and then, once trapped (2) placing the 
>>>> cursor in the formula bar and finally (3) Paste the clipboard into the 
>>>> formula bar.
>>>>
>>>>
>>>>
>>>> Has anyone any ideas please?
>>>>
>>>>
>>>>
>>>> TIA
>>>>
>>>>
>>>>
>>>> Phil
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
PPL
3/7/2010 12:37:35 AM
I don't know how to access the Formula Bar directly from
 VBA.

"PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
mzCkn.35580$_v6.30372@newsfe08.iad...
> Other than placing the project in Break mode - and displaying the VBA 
> code - the STOP statement seems to have no affect.
> Am I missing something
>
> "Mishell" <Mishell@nospam.ca> wrote in message 
> news:OhQ%231CWvKHA.5008@TK2MSFTNGP05.phx.gbl...
>>
>> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
>> vRukn.32278$Dv7.29500@newsfe17.iad...
>>> Thanks Mishell I'll try your ideas and I'll let you know
>>> Do you have any ideas on how the Formula Bar can accessed directly from 
>>> VBA - that way I should be able to obviate messing with the merged 
>>> cells?
>>
>> You could try to put a STOP statement in your code.
>>
>>>
>>> Phil
>>>
>>>
>>> "Mishell" <Mishell@nospam.ca> wrote in message 
>>> news:eL8vRuPvKHA.1796@TK2MSFTNGP02.phx.gbl...
>>>>
>>>>
>>>> To UnMerge DestinationArea before Copy.
>>>>
>>>>
>>>> Sub UnMergeDestinationAreaAndPaste()
>>>>
>>>> Dim rngToCheck As Range, rngCell As Range, rngChecked As Range
>>>>
>>>> Set origin = Sheets("Sheet2").Range("G1:I3")
>>>> Set dest = Sheets("Sheet3").Range("B1")
>>>>
>>>> Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)
>>>>
>>>> For Each rngCell In rngToCheck.Cells
>>>>
>>>> If rngCell.MergeCells Then
>>>>
>>>>  rngCell.MergeCells = False
>>>>
>>>> End If
>>>> Next rngCell
>>>>
>>>> origin.Copy dest
>>>>
>>>> End Sub
>>>>
>>>> Mishell
>>>>
>>>>
>>>> "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news: 
>>>> Oiekn.69271$Ye4.61706@newsfe11.iad...
>>>>>I do realize that merged cells are bad news.
>>>>>
>>>>> I'm stuck with an Excel 2003 worksheet, which contains this dreaded 
>>>>> feature. Users are complaining that on trying to paste text into 
>>>>> merged cells gives Excel Warnings:
>>>>>
>>>>>   "Data on the Clipboard is not the same size and shape as the 
>>>>> selected area. Do you want to paste anyway?"
>>>>>
>>>>> And:
>>>>>
>>>>>    "Cannot change part of a merged cell."
>>>>>
>>>>>
>>>>>
>>>>> Interestingly, though if the user hits F2, which places the cursor in 
>>>>> the formula bar, then the paste operation works!
>>>>>
>>>>>
>>>>>
>>>>> I'm usually reasonably of Ok with VBA; but this one is stumping me.
>>>>>
>>>>> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel 
>>>>> Application warning(s) above and then, once trapped (2) placing the 
>>>>> cursor in the formula bar and finally (3) Paste the clipboard into the 
>>>>> formula bar.
>>>>>
>>>>>
>>>>>
>>>>> Has anyone any ideas please?
>>>>>
>>>>>
>>>>>
>>>>> TIA
>>>>>
>>>>>
>>>>>
>>>>> Phil
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
Mishell
3/7/2010 1:45:24 AM
If you're just trying to assign values, you don't need to "type" the value into
the formula bar.  You can use something like:

worksheets("Sheet1").range("A1").value = worksheets("sheet2").range("z99").value

or
worksheets("Sheet1").range("a1").mergearea.cells(1).value _
  = worksheets("sheet2").range("z99").mergearea.cells(1).value



PPL wrote:
> 
> Thanks Mishell I'll try your ideas and I'll let you know
> Do you have any ideas on how the Formula Bar can accessed directly from
> VBA - that way I should be able to obviate messing with the merged cells?
> 
> Phil
> 
> "Mishell" <Mishell@nospam.ca> wrote in message
> news:eL8vRuPvKHA.1796@TK2MSFTNGP02.phx.gbl...
> >
> >
> > To UnMerge DestinationArea before Copy.
> >
> >
> > Sub UnMergeDestinationAreaAndPaste()
> >
> > Dim rngToCheck As Range, rngCell As Range, rngChecked As Range
> >
> > Set origin = Sheets("Sheet2").Range("G1:I3")
> > Set dest = Sheets("Sheet3").Range("B1")
> >
> > Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)
> >
> > For Each rngCell In rngToCheck.Cells
> >
> > If rngCell.MergeCells Then
> >
> >  rngCell.MergeCells = False
> >
> > End If
> > Next rngCell
> >
> > origin.Copy dest
> >
> > End Sub
> >
> > Mishell
> >
> >
> > "PPL" <pp1@shawRemoveThis.ca> a �crit dans le message de news:
> > Oiekn.69271$Ye4.61706@newsfe11.iad...
> >>I do realize that merged cells are bad news.
> >>
> >> I'm stuck with an Excel 2003 worksheet, which contains this dreaded
> >> feature. Users are complaining that on trying to paste text into merged
> >> cells gives Excel Warnings:
> >>
> >>   "Data on the Clipboard is not the same size and shape as the selected
> >> area. Do you want to paste anyway?"
> >>
> >> And:
> >>
> >>    "Cannot change part of a merged cell."
> >>
> >>
> >>
> >> Interestingly, though if the user hits F2, which places the cursor in the
> >> formula bar, then the paste operation works!
> >>
> >>
> >>
> >> I'm usually reasonably of Ok with VBA; but this one is stumping me.
> >>
> >> Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
> >> Application warning(s) above and then, once trapped (2) placing the
> >> cursor in the formula bar and finally (3) Paste the clipboard into the
> >> formula bar.
> >>
> >>
> >>
> >> Has anyone any ideas please?
> >>
> >>
> >>
> >> TIA
> >>
> >>
> >>
> >> Phil
> >>
> >>
> >
> >

-- 

Dave Peterson
0
Dave
3/7/2010 2:24:26 AM
Reply:

Similar Artilces:

excel save as csv
I need to generate a csv file that encloses every text field with a double quote like this - "abc", "def","ghi", etc. Currently, excel will do this ONLY with fields that contain a comma like this - "word1, word2", def, ghi Any help would be much appreciated! Take a look here: http://www.mcgimpsey.com/excel/textfiles.html In article <51B9385E-F0F9-4A0E-B336-497E2883EA2D@microsoft.com>, "Newbie-Don" <Newbie-Don@discussions.microsoft.com> wrote: > I need to generate a csv file that encloses every text field with a doub...

How do I view complete text content in an Excel Comment box?
When scrolling over comment boxes in Excel, I can only partially view note content. Is it possible to specifiy the size of the comment box OR is there any simple method available to view complete contents of comment box while scrolling over? Right-click>Edit Comment>Drag to re-size. Or re-size all using code. http://www.contextures.on.ca/xlcomments03.html#Resize Gord Dibben MS Excel MVP On Sun, 25 Apr 2010 02:41:01 -0700, Deaglan1 <Deaglan1@discussions.microsoft.com> wrote: >When scrolling over comment boxes in Excel, I can only partially view note ...

Setting cell tabulations( ranges) in excell
I would like to set a ranage for tabulation; the row starts with "Week day and ends with END". You can automatically tab from WEEK day to END but to speed things up I want the tab to go to the next row once it reaches" End". instead of going to the next empty cell on the same row. How can I set a tabulation range? Example: Week Day Date Shift Start Lunch In Lunch Out Shift End Wednesday 18-Aug-04 Thursday 19-Aug-04 If you select your cells (say B2:H999) first, you can tab between the cells in the selection. dizzy kay wrote: >...

can I sort text data into bins?
Hi, Here's my problem - I hope you can understand it & help: Let's say I've got a column, A (or row 1, it doesn't matter) tha contains cells, each with a single word/category that I'm interested i [example: dog, cat, bird, fish, gerbil] - we can assume that this is m list of category headers/titles. Plus I've also got various other columns, A-D (or rows) next to m column A titles that each contain a list of items, one per cell: [eg B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil]. Perhaps it would be clearer if I drew it out (ignore the dashes they're just t...

VB Code not grabbing cell values
hi, i was given the VB code below which takes all the value of cells V17 and X25 and create a list down columns A and K on the 'graphs' sheet, it works when i manually enter values into cells V17 and X25 and was testing it, but it doesn't work when those cells are auto populated with data as they calculate other cells, so the VB code isn't taking the values that appear in those cells automatically like it should. any ideas? Private Sub Worksheet_Change(ByVal Target As Range) Dim strColumn As String, lngRow As Long, ws As Worksheet If Target.Count <> 1 ...

Edit text in List Control
Dear friends, I have implemented a List Control using List View with the detailed style having several columns containing subitmes. Now i want to let users change subitmes listed under two columns only. Please, help me to figure out how to do this. Thank you. Varuna. http://www.codeguru.com/Cpp/controls/listview/editingitemsandsubitem/article.php/c4175/ http://www.codeproject.com/listctrl/listeditor.asp http://www.codeproject.com/listctrl/editing_subitems_in_listcontrol.asp Tom <varuna82lk@gmail.com> wrote in message news:1125679899.375109.107300@o13g2000cwo.googlegroups.com... &g...

Task Scheduler or VBA to simple copy periodically folders contents to external drive
I need to copy periodically all files (without any other data manipulation) from folders W, X, Y on Drive C to folder Z on Drive D. Any existing files would ge ovewritten without prompting. Is it more convenient to process this through Windows Task Scheduler or through VBA ? Help appreciated, J.P. If you need to work only with files, not subfolders, you can use code like the following: Sub AAA() Dim FromFolders As Variant Dim ToFolder As String Dim N As Long Dim FName As String FromFolders = Array("C:\Test", "C:\Test2") ToFo...

Flag row if cell values = something specific
Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

IE opens with "about:blank" url when I run my solution
Ever since I installed IE8 it opens with "about:blank" url when I run my solution. I close that instance and another instance opens that runs OK. I figured when that first happened that MS would soon fix it but to my knowledge they haven't. Is that correct or am I missing something or doing something wrong? Thank ...

Can I add a picture to a cell?
Is there a way to add a picture to a particular cell? This way I ca use it for look ups in other sheets. Thanks in advance for any help -- Jua ----------------------------------------------------------------------- Juan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=688 View this thread: http://www.excelforum.com/showthread.php?threadid=26356 Hi not possible as pictures 'float' over the cells -- Regards Frank Kabel Frankfurt, Germany "Juan" <Juan.1d6lzx@excelforum-nospam.com> schrieb im Newsbeitrag news:Juan.1d6lzx@excelforum-nospa...

Why do I get this ###### instead of 11:00 p.m. in formatted cell?
One reason could be that the column isn't wide enough. Try widening the column. -- Biff Microsoft Excel MVP Always supply your formula and the values of the input cells -- Kind regards, Niek Otten Microsoft MVP - Excel "new user" <new user@discussions.microsoft.com> wrote in message news:32404C0C-FB66-4D90-A301-42D0322E2CFE@microsoft.com... | On 15 =F1=E5=EF, 19:43, new user <new u...@discussions.microsoft.com> wrote= : > Not to forget: if cell contains fomula that evaluates time value and result turns to be negative, it will be shown as ###### no...

Change value of checkbox at runtime (using VBA)
Hello, I will like to change the value of a check box at runtime. I have a database with a field "multiple_print_select", which stores a value of "1" when it is checked so that the record can be printed. This allows only the records selected to be printed, but wen another set of records are selected again, the previous selection are still true, so the records are reprinted which I need to prevent. So, I'm thinking of changing the value of the checkbox at runtime: For instance, a VBA code that will set the value of the checkbox to "0" on closing the report o...

How to have a cell auto resize the font to make text fit in cell?
I'm using Office 2007, and I would like to know how to make the font size automatically adjust to fit whatever I type into an individual cell. Select the range to format hit ctrl-1 (ctrl-one key) Alignment tab Click shrink to fit (I bet that you can find the formatting option somewhere on that ribbon!) tanner08 wrote: > > I'm using Office 2007, and I would like to know how to make the font size > automatically adjust to fit whatever I type into an individual cell. -- Dave Peterson ...

Randomly Choose Cells from Colum/Row
hello. i'm not sure if this is possible, but i'm trying to randomly select multiple cells from a colum to be evaluated. for example, say i have a clumn of 10 cells. i'd like this function in another cell to select 3 cells from the 10 at random. is this possible? thanks In a column next to the column containing the ten data items enter: =RAND() and copy down. Touch F9 and then sort by the newly entered column. This will "randomize" your data. Just take the top three cells. Whenever you want a new sample, repeat the process -- Gary''s Student "J&qu...

Pasting Word table into Excel 2000
When I copy a table from Word 2002 and paste it into Excel 2000 for some reason each row in the table ends up occupying two rows i Excel. How can I get the table to paste properly -- Message posted from http://www.ExcelForum.com >-----Original Message----- >When I copy a table from Word 2002 and paste it into Excel 2000 >for some reason each row in the table ends up occupying two rows in >Excel. How can I get the table to paste properly? > > >--- >Message posted from http://www.ExcelForum.com/ > >. >Is the data really in two rows or are the row heighs en...

using the value in a cell as part of another cell reference
I want to use the value in one cell as part of the reference for another. eg. A(K10). is there an easy or any way. Pretty new to this, if I'm asking the obvious. As I can't find a function or other method. Steve Stephen, =INDIRECT("A1") refers to A1, and returns what's in A1 =INDIRECT(A1) refers to whatever A1 points to (if A1 contains "A2" then you'd get what's in A2) Your question: =INDIRECT("A" & K10) (if K10 contains 5, then you'd get what's in A5) You can also use =OFFSET(A1,K10-1,0,1,1) This one isn't confounded b...

Strip non numeric characters from a cell
Hello. I have a list of contacts that I am trying to "clean-up" the data. A column of phone numbers have been entered in a variety of different ways (e.g. 555.555.5555; (555) 555-5555; Home: 555-5555; etc.) How can I remove the non-numeric characters? Thanks in advance! -- TraciAnn On Mon, 13 Apr 2009 13:04:34 -0700, TraciAnn <TraciAnn@discussions.microsoft.com> wrote: >Hello. > >I have a list of contacts that I am trying to "clean-up" the data. > >A column of phone numbers have been entered in a variety of different ways >(e.g. 555.555.5555...

Excel 2007
Hi, I am hoping that someone can help me with two problems I am having in Excel 2007. The functionality of copying and pasting to and from filtered lists seems to be different from what I experienced in Excel 2003. Problem 1) Copying (with Ctrl-C) from one column in a filtered list into another column in the filtered list (with Ctrl-V) When I copy with Ctrl-C it selects the cells in the list that are visible (which is what I want) but when I paste those over another column in the same filtered list (I have not changed the filter settings) it pastes the data over cells that are not visible i...

Highlighting text with F8 key
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Instead of turning on text highlighting, pressing F8 on my new iMac (using Word 2008) turns on iTunes! Anyone know what I'm doing wrong or not understanding? Thanks!! I'm not sure what to tell you - F8 in Word is still assigned to the ExtendSelection command & works as expected here in 12.2.4 running on 10.6.3, so whatever is causing it must be at the OS level or external of Word. Assuming that Office & OS X are fully updated, check the System Preferences for your keyboard settings as well ...

How do I change font size in multiple text boxes?
I am working in Publisher, and I have dozens of text boxes that I want to change the font size, from say 10 to 8. Is there a way that I can select a number of them and change them in a batch-style? Use the Painter tool. Change the first text box, while your cursor is in the box, click the Painter, click the next text box and so on. Or open the font scheme dialogue, use or create a font scheme, click each text box with the text scheme highlighted. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JustinB" <JustinB@disc...

Printing question vba
I have a workbook with several worksheets , when the workbook is ready for printing I want to print out 1 page with 3 copies of three of the worksheets and 1 to 10 pages with three copies of another sheet, depending of how much data it is in that sheet. My question is. How to find out how many pages that has data in it and then just print those pages. I have data in columns A-G. I know how to record a macro to do the first thing and then go to the next sheet and do the same thing again. ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=3 Sheets("Page2").Select One ...

change text color/size/font/etc... (VC++)
When adding text to a dialog, I'd like to be able to set features like size, color, font, bold/italic , etc.. Unfortunately, the when you add the text to the dialog, it's the default basic font and style. I export the text, but I can't find an obvious way in the CStatic class to set these characteristics. I know it is possible to do. Any information would be greatly appreciated. Thanks. "axis" <news.post.3@geekhouse.no-ip.com> a �crit dans le message de news:pfXWb.298146$xy6.1467437@attbi_s02... > <snip> > > Thank you Bob, Ajay, and Aggro! All th...

A cell appears "######". What's this?
I write down a type, for example "55,000", which is by default a general type. From the menu Format, Cells, Number , I try to make it as an accounting type(in dollars), I press ok and then my type "55,000" disappears and appears a "#####" instead. What is this and how can I correct it? Thank you. Make the column wider, the "#####" means that there is not enough room to display the contents "marlen_athens" wrote: > I write down a type, for example "55,000", which is by default a general > type. From the menu Format, Cel...

How do I set the distance between text and continued notice?
Sometimes the "continued" notice appears directly under or over my text and sometimes it inserts a space. How can I make it consistent? What version Publisher are you using? Check your paragraph settings in the format menu. I've tried all sorts of formatting and cannot duplicate your issue. It always leaves a blank line between the text and the "continued..." -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Trish" <Trish@discussio...

I need to add two text boxes from two different reports.
I am adding a value from one monthly report to another monthly report. I keep getting #name. Please help! "Shawna" <Shawna@discussions.microsoft.com> > I am adding a value from one monthly report to another monthly report. I > keep getting #name. Please help! Give us a few more details. What are you typing into your text boxes? You can't actually add up things in 2 different reports - reports are just ways of showing data - I'm guessing that you are really adding up things from a table. A common cause of the #name error is when you are trying to refer to a...