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 have about 30000 lines of data that looks like so:
Where ColA is time. I am having trouble creating a graph with time on the
x-axis and count on the y-axis.
Thank you in advance.
You need to make an XY chart
But with 30,000 data points you could run into trouble; Help says you can
32,000 data points in a single data series.
However, if you want markers it will look like heck.
You could limit the data like this:
assume y-values in B2:B30001
in ...Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date
entered into cell, shows up correctly in the text entry
field at the top of the screen, but the data on the
worksheet displays as "33747", or similar number. Only
happening on one workbook.
Try tools|options|View tab|uncheck Formulas.
> Format column of cells as Date, display as mm/dd/yy. Date
> entered into cell, shows up correctly in the text entry
> field at the top of the screen, but the data on the
> worksheet displays as "33747", or similar number. Only
> happening on one...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...C# Adding Days to a Date
I have 2 objects:
objContract.activeon and objContract.expireson.
I am trying to add 364 days to objContract.activeon and assign it to the value
// Contract Start Date
DateTime dt = DateTime.Now;
objContract.activeon = new CrmDateTime();
int iFound = 0;
string sTemp = "";
if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays
objContract.activeon = objInvoice.CFDinvoicedate;
else // ...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...Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data
columns. Can the data in the columns of a chart table be right justified?
In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf-
8?B?c2FtIGVhZ2xl?= <sam firstname.lastname@example.org> says...
> Ecxel 2003 and previous versions of the product center the data in the data
> columns. Can the data in the columns of a chart table be right justified?
Have you tried to format the table? If yes, and you haven't been
successful it is probably because XL allows very limited cust...Data migration - Adventure Works
I have a company where the adventure works db has been used and had a
lot of data populated into the system. We have now purchased MSCRM and
have obtained the company reg keys.
What is the easiest way to get the data from the 1 system to the next?
We will be establishing a new AD domain and users for the new
Data Migration Framework?
Microsoft CRM MVP
"funboy...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...Label a chart of counts with other percentage data
Here's the data:
Group 2005 2006 2007 LTM Data
A 19.4% 22.8% 21.2% 19.9% Profitability
A 6 7 7 7 Count
B 9.5% 31.6% 30.4% 30.7% Profitability
B 2 3 3 3 Count
C 22.4% 23.6% 16.6% 17.6% Profitability
C 15 16 17 18 Count
D 19.2% 20.5% 15.9% 13.7% Profitability
D 8 8 9 10 Count
I have successfully generated a stacked bar chart that shows the counts per
group by year.
Now I would like to include a label for each group to show profitability for
each group in each year in the 4 stacks. How would I do that?
--...Creating a chart based on the data in an embedded worksheet
I have a worksheet with several embedded worksheets. I would like to
create a chart based on the data of one of the embedded worksheets
without putting the chart in the embedded worksheet. I have tried
unsuccessfully to do this. I just wondered if anyone knew how to do
You're embedding worksheets within worksheets? Why? Why not just insert
the worksheets in line with the main worksheet? To open or edit the
embedded worksheet, the parent Excel has to open another instance of
Excel, and the chart on the outside of this other instance will never be
able to acce...creating a spredsheet and log the info into another spredsheet
I have a excel sheet that is printed out and a cashier manual enters
information, invoice number, invoice amount, cash amount, check
amount, amex amount.... I would like to have the cashier input this
information on her PC and print a copy to go along with the deposit
and at the same time log the information into a google excel document
I created. Any ideas how this can be done?
...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
I have set up an integration mapping into the Bank Transaction Entry for GP
8.0. Since there is no option to save a bank transaction entry, it posts
automatically when I run the integration, but it does not print off the
reports I usually get when I do a manual bank trans. entry (bank transaction
posting journal & general posting journal). I was wondering if anybody knows
how to get IM or GP to print the reports associated with the entry? Do I
have to write a script?
I ran into the same issue. Boy I wish Bank Trx used batches that would solve
this. We just did a repri...Run time error 5 : HQClient
When I double click HQ Client I have message box "Run time error 5 : Invalid
procedure call or argument"
how should I do? I used RMS 2.0
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGr...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,...date function #3
This is a multi-part message in MIME format.
I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get =
5. Is there a way to return May instead? either through a command or =
formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, =
"May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and =
the 12 embedded if's in cell A3 are too long (that is the error I get =
when trying to do so), plus i...Setting appointment start & end time increments format
In Outlook 2002, I can easily set the Day View to display
the time increments in 15 minute blocks, but on the
Appointments form, the Start and End Times available on
the drop-down menus, respectively, are in 30 minute
increment blocks only. How can I set the format for the
appointment page to have the Start and End Times for
appointments have increment time blocks of 15 minutes?
Thanks! My direct email address is:
If you set your appointments in 15 minute blocks
Outlook will start to offer that. It takes a few uses
Nikki Peterson [MVP ...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" <email@example.com> 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...