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_...How do I set up a daily average of unit sales formula
More info required.
(remove nothere from the email address if mailing direct)
"jim m" <jim email@example.com> wrote in message
...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 = ""
...Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other
data. I want to save the workbook as another name with all the worksheets
keeping the values only (no links or formulas). Is there a quick way to do
this for everysheet without having to special paste every sheet in the
workbook. So can I save everysheets data values at workbook level.
See this page for a code example
Regards Ron de Bruin
"lex63" <firstname.lastname@example.org> wrote in message news:ED708...Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus,
First of all, let me thank you in advance. I find it exemplary that
you all can devote time to helping others who are having issues with
their work. Hopefully one day I can be at a mentor level, and help
Hope you can help! I have an issue where I don't know how to write the
formula that I need without going over on the nesting. The current
formula that I have is as follows:
Data",IF(V7="Yes",&qu...Hours and minutes within/outside interval
I have a spreadsheet with three columns, In colum A I have Start time in
hours and minutes and in column B I have the End time in hours and minutes.
In column C I calculate Spend time. This works perfectly OK.
Now I need a fourthand fifth column. In this I need to calculate how much of
my Spend time, lies with in the interval 06:00 to 17:00 (six AM to 5 PM).
and how much lies outside
Start time can be anywhere between 00:00 ands 23:59, and the same goes for
Start time: End time: Spend time
Within the interval ...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...Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching
forever and havn't found a hint yet.
I have multiple domains setup on my exchange server. Lets say abc.com,
def.com, and hij.com. We only send email with @abc.com but can receive
from any domain name. Recently we merged with another company and we
brought back their email to our server, lets say xyz.com. Now I
configured a new recipient on Exchange 2003, so I now have two
policies. The first policy has the three original domains in it with
@abc.com as the primary domain. In the second policy I have configured
xyz.com t...VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves
two different tabs in the workbooks as values. It needs to test and wait
until the formulas that are pulling amounts from another database are
correctly populated with the amounts.
Right now, I am getting N/A# instead of amounts for some, but not all, of
the files. I think the problem is that the formulas have not completed
pulling the amounts before the macro pastes that tab as zeros. (N/A# is what
appears when the formulas have not completed calculating - once they have
completed, the N/A# is replaced ...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...VCR to DVD Part 2
Many thanks for previous answers.
Yes, finalizing the DVD worked
I found that DVD-R need finalizing but DVD+RW disks do not
But why was I able to play the unfinalized DVD-R on one machine and not on
What was special about the first PC?
Bernard Liengme wrote:
> Yes, finalizing the DVD worked
> I found that DVD-R need finalizing but DVD+RW disks do not
> But why was I able to play the unfinalized DVD-R on one machine and not on
> two others.
> What was special about the first PC?
Almost anything will play a finalised D...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...set print area #2
Where is the "set print area" button in Excel 2002 that was in Excel '97?
> Where is the "set print area" button in Excel 2002 that was in Excel '97?
Are you sure it was "standard"? Only way I could get in was to right-click
the toolbar|Customise and drag it to the toolbar from Commands|File
...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...Help With Margin Formula
I need help with a margin forumla (calculated from retail). Say I have a
cost of $10.00, and I need the formula to calculate a 40% margin from
retail. So the retail should end up at $16.67. Not sure how to get from
$10.00 to $16.66, I just know the cost and the margin I need to make.
"JR" <email@example.com> wrote in message
> I need help with a margin forumla (calculated from retail). Say I have a
> cost of $10.00, and I need the formul...OEM publisher 2002 Sp1-2
I have the OEM version of Publisher 2002 and cannot find the Export as web page under File. Is there a way I can get that feature with a standalone disk?
After managing to set up OE-QuoteFix on his new PC, Ed reads a message
from Chicagogal <firstname.lastname@example.org>...
> I have the OEM version of Publisher 2002 and cannot find the Export
> as web page under File. Is there a way I can get that feature with a
> standalone disk?
The OEM version of Publisher is exactly the same as the release in every way
but the license, so the standalone version will be no differen...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...GP SP#2 hangs with sa and Collections Issue
I just installed 8.0 SP#2 (ran the script that it says to run cuz we had
upgraded from previous version to remove dups in smartlist tables) and when I
log into GP as sa, it hangs and CPU sits at 100%.
When I was able to log in previously to SP#2, I constantly received errors
about the CN_Collector_MAIN table with invalid column names etc. I've
deleted the table, then re-ran the create tables, but it keeps coming back
with this error. I've even gone into file > maintenance > sql and deleted
and recreated the table, but then get "number of results doesn't match ...Missing Reference
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,...Changing ip address of exchange server #2
I have a back-end server and a smtp server in DMZ.
I want to change ip address of back-end server. are there any issues?
all incoming and outgoing emails are going via smart host.
No issues at all as long as you remember to change all the references to
this server in your firewall, SMTP scanner etc.
"Jack Dorson" <JackDorson@discussions.microsoft.com> wrote in message
> I have a back-end server and a smtp server in DMZ.
> I want to change ip address of back-end server. are ...How can I print on 8 1/2"x17" paper?
I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to
change the paper size to accept and print anything bigger than 14." any
suggestions would be greatly appreciated
First make the size settings in the Printer Setup. You may need to use the
"mickilena" <email@example.com> wrote in message
>I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to
> change the paper size to accept a...when opening an excel file, 2 files open (one is book1)?
When I click on an excel file, 2 excel documents open up. One document is
called Book1 and the other document is the actual document that I want to
open. How do I stop this from happening?
To prevent Book1 from opening you can append /e to your shortcut for opening
"C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\EXCEL.EXE" /e
Alternative to the above............
Go to Start>Settings>Folder>Options>File Types. Scroll down to MS Excel
Worksheet. Then if running Win98 OS Edit> select "Open" and Edit.
If using WinXP OS you would scroll down to...Money Updates #2
Was wondering about money updates. What exactly is the program downloading
during the update process? And if the program is updated on a daily basis,
why would I ever need to upgrade to a newer version?
In microsoft.public.money, invalid wrote:
>Was wondering about money updates. What exactly is the program downloading
>during the update process? And if the program is updated on a daily basis,
>why would I ever need to upgrade to a newer version?
It is mainly changes in online bank setup.
Despite appearances is typically does not change anywhere daily.