Prevent clicking on a cell
I want to run the code below to prevent a range of cells from being selected
if the Range("Q7") = 1. I have all cells on the worksheet locked but the
user must be able to click on the locked cells to trigger a userform so I
have to check Select Locked Cells. So is there any way make the
If Range("Q7") = 1 Then
Range("B5:C5").Locked = True
>So is there any way make the
> Range("B5:C5") unselectable?
No but you can stop them staying there.
Private Sub Worksheet_...if cell is text move left one column
ColB is a long list with sections names followed by category codes
I need to move the text into colA leaving colB with codes only (all numbers)
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
If Not IsNumeric(Range("B" & lngRow)) Then
Range("A" & lngRow).Value = Range("B" & lngRow).Text
Range("B" & lngRow).Value = ""
...How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line
extend across an entire cell even if the number is only 2-3 digits and 2. How
to apply a double line under a number without using the = sign in the
Look on the formatting toolbar for Borders
Regards Ron de Bruin
"Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com...
> When working on a financial statement, I was curious how to 1. Have a line
> extend across an entire ce...Separating Date and Time in a cell
I have a column of cells in the format "11/01/02 06:21". I would like to
separate the text into 2 cells - one with the date and the other with
the time. My attempts with LEFT and RIGHT have been unsuccesful. Thanks
for your help
Message posted from http://www.ExcelForum.com/
For the date use
replace A1 with the first cell of your range
you probably have to reformat the first to
mm/dd/yy (or whatever the setting is)
Note that you can do this by just using format but if you want to compare to
other cells with just pure d...pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then
return that "certain criterea" to a new column.
Some examples of a strings of characters may look like these:
My "Certain Critera" I have listed on another sheet, named "REP ID"
How do I pull out the 3 characters of "Certain Criterea" from the string of
text and copy or enter it into a new column?
> I need to look up "c...can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame,
which is never as accurate anyway, can they be set to 'snap' to a frame (eg
the ellipse) so that they are perfectly inline, (and which would of course be
a lot quicker)?
Edit points will not snap. There are options for edit points, select a point,
right click. If you hold down control, the cursor will turn into an x, you can
delete a point with a click.
Truly a good draw program would be preferable.
Mary Sauer MSFT MVP
&q...cell contents revert to 0 when i click on the next cell
I put a number into a cell click on the next cell and the first cell reverts
to 0. If I format to number with 2 decimal places it will be ok but when I
try to take out decimal places it goes back to zero,
You haven't said what number you are trying to put into the cell, but I
suspect that the number is less than 0.5.
A quick test shows that if you set the cell to no decimal places then enter
a number less than 0.5 it is displayed 'rounded down' so it will show as
zero, if it's 0.5 or above it displays as 1.
If you need to put numbers less than 0.5 into youe c...Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this:
is there a way to adjust the formula so that an empty cell in G21 doesn't
give the #VALUE! in subsequent cells in column H?
Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for
any and all empty cells in A2 to A9. It no longer matters if any of the
cells are empty, the formula correctly gives the correct addition of A1
plust a sum of everything between A2 to A10 without any #VALUE! results.
Was hoping to have the formula above als...Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the
cell length. I wish to keep the cell lenghts for the entire document, but do
wish for teh text in that particular row to be displayed.
How about if you select that cell, then
format|cells|alignment tab|check wrap text
And with that row selected
> As I type text in some cells, it does not always dispaly if it exceeds the
> cell length. I wish to keep the cell lenghts for the entire document, but do
> wish for teh text in that particular row to be displayed.
Da...Automatic changes in cells
Hi for some reason I now have to save my work for any formlas etc to change
when I update a worsheet, how can I stop this as it is a pain and sometimes
I need to do changes to see how they work before saving the work.
Click on Tools | Options | Calculation tab and set to Automatic
calculation, as it is probably set to Manual. You can press F9 to
force a recalculation under a manual setting.
Make sure you save the file with the Automatic setting, to avoid it
happening next time.
Hope this helps.
On Feb 1, 11:42=A0am, Office 2004 Test Drive User
<heepenm...@yahoo.co.u...default text height comment
Is there a way to set the default text height for a new comment?
(I've looked through help but can't find it if it's in there.)
I assume you mean the font size? There is no text height available in Excel.
A comment has a shape property and that is what you can use to change the font size.
They didn't make it easy ...
Range("D4").Comment.Shape.TextFrame.Characters.Font.Size = 12
Portland, Oregon USA
(free and commercial excel programs)
wrote in message...Change the text of a shape rather than its master
I build custom masters by mixing two general shapes, say square and
circle together, and have text on both the shapes.
But after I drop an instance of the master into a page, I cannot
modify the text of the instance. To do so, I need to modify the text
on the master, which is non-sense for me.
How to change the text of a shape without modifying its master?
How are you doing this? By code or by the UI?
Are you grouping the shapes? If you drag two shapes to the stencil, it will
group the shapes. So instead of a square and a circle you have three shapes.
A Square, Circle and the...cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg
a qualification lasts 12 months. what i want to do is have the cell change
from yellow to orange to red as the expiry date gets closer.
If column A contains expiry dates then select column A,
=DATEDIF(TODAY(),A1,"m")<1 red for 1 month
Click Add button, formula2:
=DATEDIF(TODAY(),A1,"m")<2 orange for 2 month
Click Add button, formula3:
=DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month
Adjust number of months as you like!
Regards,...Mail Merge to business cards
I am working on a huge project for work, creating Employee Photo ID cards. I
have created a database for all the information including a hyperlink column
for the photos. and am using business cards to print them on. I have two
1. The pictures aren't loading into the cards.
2. I continue to get the error message "Publisher cannont merge with this
number of pages" even though I have followed the instructions for the
Any suggestions? Thanks
...Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at
the end of the text on the right hand side for each cell. Is there any
easy way to remove this space?
Use the TRIM() function.
"lj" <firstname.lastname@example.org> wrote in message news:email@example.com...
> I'm in excel and i have a bunch of text data that has an extra space at
> the end of the text on the right hand side for each cell. Is there any
> easy way to remove this space?
I tried using that function but the results st...Calculating on alphabetic cell content
A selection of 4 different letters in a column representing different values
to be used in a formula shall be run through. The calculated result of each
cell in the column shall be placed in the cell next to the read one that
holds the letter.
Thanks in advance.
i think you're after the COUNTIF function
with your column of letters in A1:A100
and the letter you're interested in in C1
then in D1
this will count the number of times the value in C1 occurs in your range.
If this isn't what you're after, could you type out a few examples of your
...Mail merge from excel
I am trying to merge a list of names from excel into publisher. When using
the mail merge wizard it wont let me load the names all it says is f1 and f2.
what am I doing wrong? any help is greatly appreciated.
...Sorting Cells by Colors
Is it possible to write a VBA code to sort excel cells by colors, and the
followed by other criterias, as in the normal sort?
Thank you in advance.
See Chip Pearson's Sorting By Color page at:
"swiftcode" <firstname.lastname@example.org> wrote in message
> Hi all,
> Is it possible to write a VBA code to sort excel cells by colors, and the
> followed by other criterias, as in the normal sort?...searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text
made of multiple words enabling the user to than create a pivot table using
the collected key word or words as data?
...Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav
numbers. Formatting cells to number does not work.
For example if I have:
(Sired] Tennessee 37013 (herein
I just want 37013 left.
Anybody know a function to resolve this
Message posted from http://www.ExcelForum.com
The following will strip the text from the active cell and place the number
in the adjcent cell one column to the left. If there are subsequent numbers
in the original string you will get erroneous results. Put the cursor on the
cell to be processed and run the macro.
********************************...Question for Bob Phillips re Splitting Names from Cells
You gave the answers below for splitting names from cells:
Using these formulas on this example John A Doe results in John A an
Doe, is it possible to split it to show John / A / Doe in 3 separat
cells, I know I could use the formulas again on the John A result t
split them but I'd like to do it in 1 go
If possible could...Charttitle.Text crashes (Excel 2000)
when I try to run the following code (on an existing diagram):
Dim globalLstFile as String
globalLstFile =3D "02 KOBA_XYZ_C3-06214 02- 20.7-20.8=B0C Touchprobe
=2EHasTitle =3D True
=2EChartTitle.Text =3D "KOBA Sphere Gauge S/N 38 538 (" + globalLstFile +
")" & Chr(10) & _
"color coding: red =3D Zone 1, green =3D Zone 2, blue =3D Zone 3"=
Chr(10) & _
"light color: measurements that contain spheres that are
within 20 cm of the edge of the measurement volume"
..=...How do I create upper/lower case letters in cells?
I have a large spreadsheet with names/addresses that are all capitalized. I
want to make them upper and lower case (SMITH = Smith). What's the formula?
You could create helper cells with this formula
> I have a large spreadsheet with names/addresses that are all capitalized. I
> want to make them upper and lower case (SMITH = Smith). What's the formula?
...Mail Merge Not Responding
I am having trouble print merging a large file in publisher. Every time I
start the merge it hangs and I get a "publisher not responding" error. I was
able to do the merge in Pub 2003 before I installed SP3. I had the same
problem with SP2 but I was able to reinstall publisher and it worked fine.
Since I installed SP3 its not working at all and I can't remove SP3.
BTW I have the same issue in Pub 2007. Thanks
Is this the complete error?
You receive a Publisher cannot continue error message when you try to print a
mail merge in Publisher 2003
http://support.microsoft....Report: Cell #1, Cell #2, Cell #3, Cell #4
I am stuck again and would love som help :(
I would like to repeat all words found inside ~25 cells, separated only
by ", ", ignoring empty cells.
A1: [Apple ]
A5: [Syrup ]
A6: [ ]
A7: [ ]
A8: [ ]
The result should be something like:
[Apple, Orange, Banana, Tomato, Syrup]
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?thre...