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 = ""
I am trying to concatenate two fields and then add text in brackets to the
end. Here is what I have:
StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)")
I want the end result to be: Smith, Mary (Mgr)
How do I achieve this?
What you've done will work. You can also use the "+" operator if there is a
possibility that there will be a missing first name:
StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)")
That will avoid an extra spa...fiscal year setting question
i know that the fiscal year settings get more or less set in stone, so i had
a quick question before i set it for my org.
in the template field, what is that used for? if i select 'quarterly', does
that mean i can only run reports on a quarter basis? what if i wanted to do
ultimately, my question is what is the 'template' field used for?
Fiscal year setting must be aligned to your company's accountig policy.
Fiscal year setting primarily defines sales quotas. When you set quarterly
periods, you can set quarterly quotas for mem...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...Reference Book
I'm a Win32 programmer, and I'm trying to get into some MFC. I've read a
little about MFC, but the books lack a lot of quality and descriptive text
about the structure of MFC. Can I ask whose book is the Charles Petzold of
I haven't seen a bad MFC book in a long time, but then again, I haven't
bought a new MFC book in many years.
My presonal perference are the MFC books published by Wrox Press
You can't go wrong with the Microsoft Press ones either.
Al...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...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...a few basic question about resource files
I have an application that uses resource files to contain the string values
for each language.
I have created the required resource files in my project, but I don't speak
the languages I wish to have resources for.
What I would like to do, is somehow allow the end user to edit the resource
file themselves so they can set the string values as needed.
Is there a way to edit the resource files that ship with my application and
get installed ?
Is there a way to have external resource files (not embedded into my
application) that can be updated/replaced with o...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...Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific
words in the e-mail header. I set up this rule to search for sober.worm
since I'm receiving a copious amount of spam e-mails which include the
sober.worm attachment. I set the rule to delete the e-mails indefinately
but it does not delete it. It just sends the e-mails to the junk mail
folder. I would like to have it delete the e-mails forever. What can I do
to achieve this?
Nocturnal @ http://www.randomfix.com
Also, I did get it to work a few times but the rule stops working.
Another thing is if ...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
There was a missing reference, " Microsft DAO2.5/3.5
I unselected it and backed out - now, every date field is
filled with today's date and several other fields have
gone to the 'dark side'. The $ field shows $0.00 for
everyone and another field is blank.
I went back into the dialog box and I can't find that
reference in the list to reselect it.
Thank goodness I experimented using a copy, eh?
>Your references are probably messed up.
>If any of the selected references have "MISSING:" in
front ...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,...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
...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.
********************************...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?
...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...Time Format Question
I currently have time entered like this:
I need to look like this:
I've done a ton of research but nothing has worked so far.
With your values in column AA, try:
Gary''s Student - gsnu201003
> I currently have time entered like this:
> I need to look like this:
> 5:35 p.m.
> 9:02 a.m.
> I've done a ton of research but nothing has worked so fa...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?
What causes url links not work? I'm experiencing problems
with it, and cannot get them to show the URL Links.
Clicking the Links doesn't help.
...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...Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query,
the results come back with multiple lines of data for an order because of
multiple critereas in another field. Here is an example of the data returned
to my query:
A In Process
B In Process
C In Process
I would like to see only the data for orders that have not been shipped, and
totally exclude data for orders that have been sent. In the example above, I
would like my query results to show Orders B and C, but no data for A since
it has al...Two calendar dates in one cell..??
I have a Microsoft Publisher calendar document that was created in 2005 that
shows the dates Monday - Sunday. When I print it, it will not print out any
that fall after the fifth week (eg January 2006 it prints up to January 29th
but not the 30th or 31st). These are in an "overflow" area. But when I open
the calendar part in Word to edit the calendar, it appears normally and that
sixth week shows up just fine. I need that sixth week to print OR I need to
do the split date feature (I would prefer the split date).
How can I set up the numbers in one of those cells so i...