| Article | Body |
| 460 |
I have several users who are using Excel XP on Dell
GX240's running Win98 & the X button in the corner and the
Close option under File are greyed out. I'm not sure what
is causing this & I can't find anything on the KB.
Any ideas?
Thanks!
|
| 461 |
I have one user who is using Excel XP on Win98 who is
having problems with his toolbar customizations. Every
time he puts in a new toolbar, like Drawing, it disappears
the next time he opens Excel. I can't find anything in
the KB about this.
Any Ideas?
Thanks!
|
| 484 |
Hi Michael,
Don't have an answer for you but would certainly
check computer with Find for *.xlb files to see which
is updated last. Is the person on a network, are any
of the .xlb files read only (check file properties).
Read only could occur from copying from read only
media such as a CD and depending on how copied.
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Michael Roth" <mroth@seibertpowder.com wrote in message news:111cb01c21924$27be3890$39ef2ecf@TKMSFTNGXA08...
I have one user who is using Excel XP on Win98 who is
having problems with his toolbar customizations. Every
time he puts in a new toolbar, like Drawing, it disappears
the next time he opens Excel. I can't find anything in
the KB about this.
Any Ideas?
Thanks!
|
| 669 |
I have a spreadsheet with several combo boxes. I often
get the message "Not Enough System Resources to Display
Completely" when I click on one of the combo boxes to
select an item from the drop-down list.
I have tried it on my machine at work (256 MB RAM, Windows
NT, running Excel 97) and my laptop (128 MB RAM, Windows
XP Professional, running Excel XP), and I get the same
error on both machines. I am struggling to find a
solution, wondering if this has happened to anyone else
and if there is a solution. Any help is very much
appreciated.
Thanks,
Dan Winterton
|
| 686 |
Make sure the zoom is set to 100% Not being at 100% is a common cause of
memory problems, but not an exclusive cause.
Dan <daniel_winterton@hk.ml.com wrote in message
news:101b801c21be8$348047a0$36ef2ecf@tkmsftngxa12...
I have a spreadsheet with several combo boxes. I often
get the message "Not Enough System Resources to Display
Completely" when I click on one of the combo boxes to
select an item from the drop-down list.
I have tried it on my machine at work (256 MB RAM, Windows
NT, running Excel 97) and my laptop (128 MB RAM, Windows
XP Professional, running Excel XP), and I get the same
error on both machines. I am struggling to find a
solution, wondering if this has happened to anyone else
and if there is a solution. Any help is very much
appreciated.
Thanks,
Dan Winterton
|
| 698 |
Hi,
Some things to do:
Options to try and open a corrupt file
- Set calculation to manual
- open the file, but disable macros (assuming you've set
macro security to medium: Tools, Macro, security)
- As soon as you've clicked the disable macros button,
press control-pageup or control-pagedown, thus changing
sheets.
If that does not work, try creating a link to the file:
='c:\my documents\[MyFileName.xls]Sheet1'!A1
and copy right and down. This at least gets you the
worksheets values.
Sometimes the Excel viewer (or Word) enables you to open
the file and copy information out of it.
Also, Excel XP can sometimes repair XL files with trouble.
Lastly: Download the office suite from www dot sun dot com
slash staroffice (awkward spelling to hopefully avoid
another autodeletion of posting...) it's a killer app for
file recovery.
Regards,
Jan Karel Pieterse
Excel TA/MVP
-----Original Message-----
Dear MS Support Team,
I am having problem with one of the excel s/sheet I used
until Friday evening. I am using MS-Excel 97 SR-1 and this
s/sheet worked perfectly until Friday evening. It is a
very simple data-only s/sheet..no macros/ scripts/ program
at all. I saved this successfully on my 'C' drive on
Friday night and tried to open it this morning but got a
message box (attached in the file) with the following
error - "Error Message : Address: excel.exe - Application
Error"
It doesn't even allow me to open the s/sheet. I have
checked this file for virus and there are none.
Would appreciate if you can help me in this matter as I
spent considerable amount of time preparing this s/sheet.
Regards,
Ash
.
|
| 1111 |
Just a note for Excel XP...
DeriveMonthName = MonthName(6, True)
For June, and where 'True" is for abbreviated name
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"" <DavidH@OzGrid.com wrote in message
news:5qQS8.20$0O6.40826@vicpull1.telstra.net...
Hi Bill
Just an added bit to Johns suggestion, look into using Sheet CodeNames and
consider using:
DeriveMonthName = Format(Dateserial(2002,intMonth,1),"mmm")
To replace the Select Case Statement, will make you life a lot easier. I
would also suggest naming the cell C2 and simply refer to it by the name
chosen name.
If Isdate(Range("DateCell")) then
DeriveMonthName = Format(Dateserial(2002,Range("DateCell"),1),"mmm")
End if
I'm thinking you can shorten you whole code to a few lines only.
--
FREE EXCEL NEWSLETTER
/News/2home.htm
"Bill" <wapfu@xtra.co.nz wrote in message
news:1134101c21e2c$39a7ae80$36ef2ecf@tkmsftngxa12...
| I can export/import to excel the following data from an
| access query.
| Sum Of Value Count Of MarketSales Expr1
| $49.00 2 6
|
| Market SumOfNZD Expr1
| Australia 14456.75 6
| Europe 9500.00 6
| Indian Sub Cont. 1527.14 6
| New Zealand 76848.53 6
| North America 80000.50 6
| S.E. Asia 502.60 6
| South America 116628.57 6
|
| Where 6 is month integer.
| I Have the following code to convert the month integer to
| alpha.
| Function DeriveMonthName(intMonth As Integer) As String
|
| IntMonth = Worksheets
| ("MarketSalesInquiryInstantInvoice").Cell(C2)
|
| Select Case intMonth
| Case 1
| DeriveMonthName = "Jan"
| Case 2
| DeriveMonthName = "Feb"
| Case 3
| DeriveMonthName = "Mar"
| Case 4
| DeriveMonthName = "Apr"
| Case 5
| DeriveMonthName = "May"
| Case 6
| DeriveMonthName = "Jun"
| Case 7
| DeriveMonthName = "July"
| Case 8
| DeriveMonthName = "Aug"
| Case 9
| DeriveMonthName = "Sept"
| Case 10
| DeriveMonthName = "Oct"
| Case 11
| DeriveMonthName = "Nov"
| Case 12
| DeriveMonthName = "Dec"
| Case Else
| DeriveMonthName = "???"
| End Select
| End Function
|
| And I want to use the following code to transfer data
| Sub InsertDataMainInstant()
| 'Check the value of the Month number in Cell C2 of the
| sheet
| '("MarketSalesInquiryInstantInvoiceSumQuery") and
| Convert to Alpha
| 'Using Function DeriveMonthName.
| 'Dependent on the output, place the value of Cell A2
| 'from sheet MarketSumOfValueInstantInvoice Cell A2
| 'To The correct Cell in Sheet REDBOOK 2002-2003
|
| Procedure = DeriveMonthName
|
|
| Select Case Worksheets
| ("MarketSalesInquiryInstantInvoice").Cell(C2)
|
| Case Is = "Jan"
| Worksheets("REDBOOK 2002-2003").Cell(B5) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
|
| Case Is = "Feb"
| Worksheets("REDBOOK 2002-2003").Cell(B6) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Mar"
| Worksheets("REDBOOK 2002-2003").Cell(B7) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Apr"
| Worksheets("REDBOOK 2002-2003").Cell(B8) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "May"
| Worksheets("REDBOOK 2002-2003").Cell(B9) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Jun"
| Worksheets("REDBOOK 2002-2003").Cell(B10) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "July"
| Worksheets("REDBOOK 2002-2003").Cell(B11) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Aug"
| Worksheets("REDBOOK 2002-2003").Cell(B12) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Sept"
| Worksheets("REDBOOK 2002-2003").Cell(B13) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Oct"
| Worksheets("REDBOOK 2002-2003").Cell(B14) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Nov"
| Worksheets("REDBOOK 2002-2003").Cell(B15) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
| Case Is = "Dec"
| Worksheets("REDBOOK 2002-2003").Cell(B16) =
| Worksheets("MarketSalesInquiryInstantInvoice").Cell(A2)
|
| End Select
| End Sub
| But I can't get it to work, Please help.
|
|
| 1123 |
If you have strictly positive numbers:
[9999999.99]##\,##\,##\,##0.00;[99999.99]"##\,##\,##0.00;"##,##0.00
(This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in
Excel XP see function BhatText as in Thai Bhat currency.
This is the HELP as seen in Excel 2002 [I do not have 2002 on my computer]
/assistance/offhelp/offxp/excel/xlmain10/html/Bahttext.htm
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Niek Otten" <nicolaus@xs4all.nl wrote in message news:eiW8i1DICHA.2060@tkmsftngp11...
Enclose the commas in (double) quotes, like:
_-$* ##","##","##","##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-
--
Regards,
Niek Otten
Microsoft MVP - Excel
"CD" <soxplato@yahoo.com wrote in message
news:118c001c2203b$e94ea580$9ee62ecf@tkmsftngxa05...
Hi
I created a custom format and positioned the commas to
where I wanted them. But the comma positions keep getting
reset to the thousand's place.
This
_-$* ##,##,##,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-
gets automatically changed to
_-$* ###,###,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-
Any help would be appreciated. Thanks.
|
| 1214 |
Rob
Installing SBE tends to put lots of lovely add-ins and templates in the
system, which are a pain when changing versions. If you no longer need
them, load the new version of office and look for any strange .xla files or
.dot files. These will be called things like sbfm.xla. (Small business
financial manager), etc.
If you still have these, start the process of removing them, by opening
apps, Excel for example, going to ToolsAdd-ins and unchecking them so they
don't load. This is probably safer than attempting to delete the add-ins,
templates, etc.
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"rob" <peterson@owc.net wrote in message
news:#REsieSICHA.1600@tkmsftngp13...
I had Office 2000 SBE installed and had made many custom templates. I
installed Office 2000 PRO so I could get Access. Now I can't get VB to
work? If I go into templates and try to run Calendar wizard I get "Errors
occurred during load" then "Word could not fire the event". Trying to work
with macros in VB (templates in Word) gives me"Could not open macro
storage"
KB had me unregister vbe6.dll file and rename and recreate. This didn't
help.
What should I do? I have uninstalled ALL office and reinstalled just the
PRO. NO Good. Same with the SBE.
????
thanks.
|
| 1244 |
Hi Annette
Just change the TextBox CellLink property to any cell then spell check the
cell.
--
\
Get 8 Add-ins in one! For less than the price of 1
/Services/AddinExamples.htm
FREE EXCEL NEWSLETTER
/News/2home.htm
"Nick Hodge" <nick_hodge@btinternet.com wrote in message
news:umTexsUICHA.2052@tkmsftngp08...
| Annette
|
| This is not possible, but you could almost certainly drop it into a sheet
| with VBA code, spell check that and then advise user if incorrect
|
| --
| HTH
| Nick Hodge
| Southampton, England
| Microsoft MVP - Excel
| nick_hodge@btinternet.com
|
| Excel XP\WinXP
| "Annette Balboa" <abalboa@whopper.com wrote in message
| news:124d801c22144$85e1b690$a4e62ecf@tkmsftngxa06...
| I want to run spell check to text that is inside of a
| textbox, is there any way to do this??
| I have a spreadsheet setup with instructions in a cell and
| then I provide a textbox to be completed with the answer.
| Several user have asked how they could run a spell check
| to what they have written. I have tried and have not been
| able to do so.
|
|
|
| 1333 |
AP
The following code uses early binding, so requires a reference to be set up
in advance to the Word x Object Library (In the VBE, ToolsReferences)
Sub openWordDoc()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc")
wdApp.Visible = True
End Sub
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"AP" <APhang@whispertech.co.nz wrote in message
news:0MwU8.512$JN4.94385@news02.tsnz.net...
I would be grateful if someone could tell me the VBA command to
open/display
a MS Word document while in Excel.
I could create a hyperlink to the Word file and then write a command to
press ENTER on the hyperlink, but there must be a cleverer way of doing
it.
Any help would be appreciated.
Thanks,
AP
|
| 1408 |
I did a search and took care of them. I uninstalled and did a completely new
Off Pro install. I still get "COULD NOT OPEN MACRO STORAGE" when I go into
Macro, Visual Basic when I have a template loaded. Any other ideas????
thanks.
"Nick Hodge" <nick_hodge@btinternet.com wrote in message
news:uRl83sUICHA.2052@tkmsftngp08...
Rob
Installing SBE tends to put lots of lovely add-ins and templates in the
system, which are a pain when changing versions. If you no longer need
them, load the new version of office and look for any strange .xla files
or
.dot files. These will be called things like sbfm.xla. (Small business
financial manager), etc.
If you still have these, start the process of removing them, by opening
apps, Excel for example, going to ToolsAdd-ins and unchecking them so
they
don't load. This is probably safer than attempting to delete the add-ins,
templates, etc.
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"rob" <peterson@owc.net wrote in message
news:#REsieSICHA.1600@tkmsftngp13...
I had Office 2000 SBE installed and had made many custom templates. I
installed Office 2000 PRO so I could get Access. Now I can't get VB to
work? If I go into templates and try to run Calendar wizard I get
"Errors
occurred during load" then "Word could not fire the event". Trying to
work
with macros in VB (templates in Word) gives me"Could not open macro
storage"
KB had me unregister vbe6.dll file and rename and recreate. This didn't
help.
What should I do? I have uninstalled ALL office and reinstalled just the
PRO. NO Good. Same with the SBE.
????
thanks.
|
| 1453 |
Thanks, Nick.
I'm just a beginner and a lot of terms are strange to me. Hope you'll be
able to shed some light on this one. The GetObject help file gives the
following example:
-----------------------------
Dim CADObject As Object
Set CADObject = GetObject("C:\CAD\SCHEMA.CAD")
When this code is executed, the application associated with the specified
pathname is started and the object in the specified file is activated.
-----------------------------
I've adapted the example to display a word file, as follows:
Dim word_document As Object
Set word_document = GetObject("C:\test.doc")
Although no errors occur, it doesn't quite bring up the word document.
Basically nothing at all happens! Would appreciate any help.
Cheers,
AP
"Nick Hodge" <nick_hodge@btinternet.com wrote in message
news:uBaTxzlICHA.2408@tkmsftngp13...
AP
The following code uses early binding, so requires a reference to be set
up
in advance to the Word x Object Library (In the VBE, ToolsReferences)
Sub openWordDoc()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc")
wdApp.Visible = True
End Sub
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"AP" <APhang@whispertech.co.nz wrote in message
news:0MwU8.512$JN4.94385@news02.tsnz.net...
I would be grateful if someone could tell me the VBA command to
open/display
a MS Word document while in Excel.
I could create a hyperlink to the Word file and then write a command to
press ENTER on the hyperlink, but there must be a cleverer way of doing
it.
Any help would be appreciated.
Thanks,
AP
|
| 1503 |
Thanks Nick!
I went back to Excel and selected the thickest border and got the desired
result which appears to survive re-publishing.
John D. Peterson
jdpeterson@glpwgroup.com
"Nick Hodge" <nick_hodge@btinternet.com wrote in message
news:uwLu5XtICHA.2232@tkmsftngp12...
John
Borders don't seem to transfer well in XL to HTML. Open the resulting
file
in notepad and look for the Border setting. Increasing the number
parameter
may help.
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"John Peterson" <johndpeterson@hotmail.com wrote in message
news:Oxutl2rICHA.2060@tkmsftngp11...
Using the File/Save as Web Page.../Publish command in Excel 2002 SP-1 on
a
cell range, everything works as advertised except that the heavy outline
border that I created around the range does not appear in the published
web
page, only a thin line, although it is certainly present in the
worksheet.
The interior grid lines in the worksheet do transfer to the published
web
page.
I tried creating an outer heavy border around adjacent surrounding
cells,
thinking that the now interior heavy border was just getting clipped,
but
that was not the case. The line thickness just gets lost and I just end
up
with a thin double line frame.
Do I need to set some obscure flag somewhere to get the border
formatting
to
completely carry through to the published data? I didn't see anything at
Tools/Options/Web Options... that appeared to apply. Or is this a
"feature"?
John D. Peterson
jdpeterson@glpwgroup.com
|
| 1722 |
I'm experiencing a very bizarre problem. I'm an Excel consultant but I have
never seen anything like this. When I open the Format cells dialogue, I only
see 3 tabs (Border, Patterns, and Protection) instead of the usual 6. If I
select one of them, I can see the 3 other tabs(Number, Alignment, and Font).
The box almost looks like it is narrower and is using 2 rows of tabs. I have
used "detect and repair" and have done a full uninstall and reinstall. This
is Excel XP running on Windows 2000. The situation has not always been this
way with Excel on this PC. Please Advise !!!
Richard Choate, CPA
|
| 1741 |
Woops!
am using excel xp sp1 with win 2k sp2
I'm not very keen on the 'review toolbar' and dont use it
every time it pops up I disable it
however its always recreating itself
How can I control it to my liking?
Regards
David M Wicker
|
| 1806 |
Excel 2002 uses the same fileformat as 97 and 2000..
--
Regards,
Peo Sjoblom
"Tng Hau" <thau@gwbakeries.com wrote in message
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the size
of the file. Thanks.
|
| 1807 |
--
---
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Tng Hau" <thau@gwbakeries.com wrote in message news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the size
of the file. Thanks.
|
| 1808 |
LOL.
That was the second one today David
--
Regards,
Peo Sjoblom
"" <dmcritchie@msn.com wrote in message
news:uLcQkt3JCHA.2340@tkmsftngp12...
--
---
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Tng Hau" <thau@gwbakeries.com wrote in message
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the size
of the file. Thanks.
|
| 1812 |
That's what I thought. But why did we get the message
about "This file was created using a later version of
Excel ....". Why did it keep coming up even after we
clicked yes (to lose any new information)?
-----Original Message-----
Excel 2002 uses the same fileformat as 97 and 2000..
--
Regards,
Peo Sjoblom
"Tng Hau" <thau@gwbakeries.com wrote in message
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved
in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new
information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base
to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the
size
of the file. Thanks.
.
|
| 1813 |
the only way I heve solved it to copy the sheets and modules to a new
workbook in Excel 97 and save that one.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the size
of the file. Thanks.
|
| 1821 |
Thanks. I thought about that. But I have a lot of macros
and forms and range names that could become a litte messy.
-----Original Message-----
the only way I heve solved it to copy the sheets and
modules to a new
workbook in Excel 97 and save that one.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved
in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new
information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base
to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the
size
of the file. Thanks.
.
|
| 1824 |
Do a save as and in the file type box - what is listed there? Microsoft excel workbook
(.xls)?
"Tng Hau" <thau@gwbakeries.com wrote in message
news:1491901c22784$c6a69f10$2ae2c90a@hosting.microsoft.com...
: Thanks. I thought about that. But I have a lot of macros
: and forms and range names that could become a litte messy.
:
: -----Original Message-----
: the only way I heve solved it to copy the sheets and
: modules to a new
: workbook in Excel 97 and save that one.
:
: Regards Ron
:
: "Tng Hau" <thau@gwbakeries.com schreef in bericht
: news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
: I am the only one in my office so far to have the XP
: version of Excel. My problem is that any file I saved
: in
: XP will be upgraded to the XP. When other users use my
: files and try to save them, they would get a message
: saying the file was created using a later version of
: Excel, if you click yes you will lose the new
: information,
: I told the users to go ahead click yes. But the message
: keeps coming back no matter how many times they do that.
: Does any one how to get rid of that? I could not find
: anything in the Excel XP help or in the Knowledge Base
: to
: help me solve this problem. Excel XP does not have an
: option to save file as Excel 97 type. It only has
: a "Excel 97-2000 & 5.0/95 workbook" which doubles the
: size
: of the file. Thanks.
:
:
: .
:
|
| 1827 |
no problem
open the workbook and a new one
rightclick on a sheetab and choose select all sheets
select move or copy and choose in "to book" the new workbook.
select copy!! also
then OK
now all sheets ar in the new workbook
hit alt f11
in the VBA editor you can drag and drop the modules and userforms to the new
workbook.
only the code that is under thisworkbook you must copy in the thisworkbook
of the new file.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1491901c22784$c6a69f10$2ae2c90a@hosting.microsoft.com...
Thanks. I thought about that. But I have a lot of macros
and forms and range names that could become a litte messy.
-----Original Message-----
the only way I heve solved it to copy the sheets and
modules to a new
workbook in Excel 97 and save that one.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved
in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new
information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base
to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the
size
of the file. Thanks.
.
|
| 1849 |
You wouldn't have just the Excel Viewer, would you?
It's limitations can be found here.
Excel 97/2000 Viewer: Spreadsheet Files
/downloads/2000/xlviewer.aspx
(Side note: I didn't think Microsoft offers an Excel XP viewer yet...)
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Leanne" <lusciouslea@uboot.com wrote in message
news:1656701c22689$35999d70$39ef2ecf@TKMSFTNGXA08...
I'm actaually getting a headache from this, how do I
download the WHOLE excel product because, for some
reason, I don't have it! So how do I get it?
|
| 1854 |
Hi Tng,
Excel 97 through XP should be compatible, or should they,
well maybe not, here is a little item (if 13MB is little) that I found
looking for an XP viewer. Glad it's not my problem.
Office XP Converter Pack (13 MB download)
/downloads/2002/ConvPack.aspx
The Office XP Converter Pack can be useful to users or
organizations that use Microsoft Office XP in a mixed
environment with previous versions of Office, including Office
for the Macintosh or other Office-related productivity applications.
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Tng Hau" <thau@gwbakeries.com wrote in message news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I saved in
XP will be upgraded to the XP. When other users use my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new information,
I told the users to go ahead click yes. But the message
keeps coming back no matter how many times they do that.
Does any one how to get rid of that? I could not find
anything in the Excel XP help or in the Knowledge Base to
help me solve this problem. Excel XP does not have an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the size
of the file. Thanks.
|
| 1869 |
Thanks everyone. This is what I ended up doing. I had to
tweak the VBA codes because many of the form objects
(combo boxes, list boxes etc. were created with different
names). Everyting is fine now. But I think this is a bug
(in Excel 97). As soon as the file is re-saved to 97
format, that annoying message should completely
disappear. Also, I think Excel XP should have Excel 97 as
one of the formats (not the 97-2000,5.0/95 combo format)
listed in the save as type.
-----Original Message-----
no problem
open the workbook and a new one
rightclick on a sheetab and choose select all sheets
select move or copy and choose in "to book" the new
workbook.
select copy!! also
then OK
now all sheets ar in the new workbook
hit alt f11
in the VBA editor you can drag and drop the modules and
userforms to the new
workbook.
only the code that is under thisworkbook you must copy in
the thisworkbook
of the new file.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1491901c22784$c6a69f10
$2ae2c90a@hosting.microsoft.com...
Thanks. I thought about that. But I have a lot of
macros
and forms and range names that could become a litte
messy.
-----Original Message-----
the only way I heve solved it to copy the sheets and
modules to a new
workbook in Excel 97 and save that one.
Regards Ron
"Tng Hau" <thau@gwbakeries.com schreef in bericht
news:1456701c22776$9ab87800$37ef2ecf@TKMSFTNGXA13...
I am the only one in my office so far to have the XP
version of Excel. My problem is that any file I
saved
in
XP will be upgraded to the XP. When other users use
my
files and try to save them, they would get a message
saying the file was created using a later version of
Excel, if you click yes you will lose the new
information,
I told the users to go ahead click yes. But the
message
keeps coming back no matter how many times they do
that.
Does any one how to get rid of that? I could not
find
anything in the Excel XP help or in the Knowledge
Base
to
help me solve this problem. Excel XP does not have
an
option to save file as Excel 97 type. It only has
a "Excel 97-2000 & 5.0/95 workbook" which doubles the
size
of the file. Thanks.
.
.
|
| 1872 |
Dana DeLouis <ng_only@hotmail.com wrote...
...
(Side note: I didn't think Microsoft offers an Excel XP viewer yet...)
...
Why should they if (1) the viewer can't run macros or recalc anything and
(2) the XLS file format hasn't changed (supposedly) since 97? I doubt
SmartTags would be live even if visible.
|
| 1899 |
I keep getting an error message
" no more new fonts may be applied to this workbook "
Q1) Can the number of fonts allowed be increased ?
Q2) How many am I allowed ?
Q3) Will Excel XP allow me more ?
Any help would be appreciated.
|
| 1903 |
"Kevin" <khunter2@ford.com wrote in message
news:aggn6s$18f5@eccws12.dearborn.ford.com...
I keep getting an error message
" no more new fonts may be applied to this workbook "
Q1) Can the number of fonts allowed be increased ?
Q2) How many am I allowed ?
Q3) Will Excel XP allow me more ?
Any help would be appreciated.
Kevin,
I experienced similar problems, when I had approx 150 charts in one workbook
and tried to modify their fonts. Not sure if your case is similar, but
here's a post from Trevor Shuttleworth that helped me out. I vaguely recall
that the workaround in my case was to disable the font size autoscale
thingy.
HTH
Jouni
"Trevor Shuttleworth" <Trevor@shucks.demon.co.uk wrote in message
news:uQOKo40BCHA.864@tkmsftngp04...
Jouni
From TechNet ...
PSS ID Number: Q215573
Article last modified on 10-26-2000
WINDOWS:2000
======================================================================
--------------------------------------------------------------------------
--
---
The information in this article applies to:
- Microsoft Excel 2000
--------------------------------------------------------------------------
--
---
SYMPTOMS
========
When you copy a worksheet with embedded chart objects several times, you
may
receive the following error message:
No more new fonts may be applied in this workbook.
When you click OK to clear the error message, you may receive the
following
error
message:
Cell table Integrity failure.
CAUSE
=====
This problem may occur if all of the following are true:
- You created a chart object in the worksheet.
-and-
- The chart object contains a title.
-and-
- You pasted a chart object on the same worksheet ten or more times.
-and-
- You copied the worksheet several times in the same workbook.
WORKAROUND
==========
To workaround this behavior, use either of the following methods.
Method 1: Disable Autoscaling in the Chart
------------------------------------------
1. Select the chart.
2. On the Format menu, click Selected Chart Area.
3. Click the Font tab.
4. Click to clear the Autoscale check box.
5. Click OK.
Method 2: Split the Number of Charts into Multiple Workbooks
------------------------------------------------------------
STATUS
======
Microsoft has confirmed this to be a problem in the Microsoft products
listed at
the beginning of this article.
Additional query words: XL2000
======================================================================
Keywords : kbdta
Version : WINDOWS:2000
Issue type : kbbug
Solution Type : kbpending
============================================================================
=
Copyright Microsoft Corporation 2000.
Regards
Trevor
"Jouni" <asd.asd@asd.asd wrote in message
news:ot9J8.5703$ws6.121035@news2.nokia.com...
Hi,
my problem seems unambiguous and maybe there's no cure for it.
Anyway, I've got five workbooks in total: four having charts on several
worksheets, and one that is used to collate the charts. That is, I copy
each
of the charts one by one (using macros, of course) and paste them into
the
fourth workbook. Because the charts tend to change a bit when pasted, I
have
to format axes (font sizes & types, and date-format). The problem is
that
I
end up having appr 150 charts on one sheet, and that's apparently too
much
for Excel2000 because I get a message "Unable to set the Size property
of
the Font class". If I try to change any fonts manually, I get a message
saying: "No more new fonts may be applied in this workbook."
Any ideas how to overcome this? I've tried to format the charts before
pasting them as _pictures_, but it didn't help because the fonts are
still
messed up and then, having the charts as pictures, the fonts are
definitely
untouchable. It's a little irritating that I have to send out a report
that
has fonts of size 3.75...
Any help appreciated.
Cheers,
Jouni
|
| 1920 |
Hi,
Your problem is not related to XP or 2000. Your errors
are due to the File Type Association. The My.xls error is
due to that your shortcut tries to open a file called
My.xls because there is a space between C:\My Doc... The
second error is due to the same in that your file is
located under a directory structure that will also contain
spaces. Therefore its the File Type Association that needs
to be updated. You can test it by putting an Excel file
under the C:\Temp directory and run the shortcut from
there.
Best regards
KM
-----Original Message-----
hello
have Excel 2000 on my machine, and installed Excel XP
recently aswell - I
use both as I have to train them.
anyway, I prefer to use 2000 as my default, but when I
set it to do so, and
then open a workbook via a desktop shortcut, it gives me
2 error messages:
1) it can't find D:\My.xls
But I don't have, nor ever created such a file on my
machine
2) it can't find D:\my documents\........\Budget.xls
which is for the workbook I'm trying to open, BUT then it
still opens it?
Any idea why this is happening? If I set XP to be the
default app, no
problems.
TIA
Fuddam
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system ().
Version: 6.0.373 / Virus Database: 208 - Release Date:
01/07/2002
.
|
| 2046 |
AP
This method uses late binding, where you do not need to set a reference in
advance. It is also slower.
What you are doing is setting a reference to the object, which in this case
is a word document, not a word application. To see what you have opened,
you will need to make the application visible after you have opened the
document in memory, e.g.
word_document.Parent.Visible = True
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"AP" <APhang@whispertech.co.nz wrote in message
news:ZkMU8.650$JN4.171550@news02.tsnz.net...
Thanks, Nick.
I'm just a beginner and a lot of terms are strange to me. Hope you'll be
able to shed some light on this one. The GetObject help file gives the
following example:
-----------------------------
Dim CADObject As Object
Set CADObject = GetObject("C:\CAD\SCHEMA.CAD")
When this code is executed, the application associated with the specified
pathname is started and the object in the specified file is activated.
-----------------------------
I've adapted the example to display a word file, as follows:
Dim word_document As Object
Set word_document = GetObject("C:\test.doc")
Although no errors occur, it doesn't quite bring up the word document.
Basically nothing at all happens! Would appreciate any help.
Cheers,
AP
"Nick Hodge" <nick_hodge@btinternet.com wrote in message
news:uBaTxzlICHA.2408@tkmsftngp13...
AP
The following code uses early binding, so requires a reference to be set
up
in advance to the Word x Object Library (In the VBE, ToolsReferences)
Sub openWordDoc()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="C:\Test.doc")
wdApp.Visible = True
End Sub
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"AP" <APhang@whispertech.co.nz wrote in message
news:0MwU8.512$JN4.94385@news02.tsnz.net...
I would be grateful if someone could tell me the VBA command to
open/display
a MS Word document while in Excel.
I could create a hyperlink to the Word file and then write a command
to
press ENTER on the hyperlink, but there must be a cleverer way of
doing
it.
Any help would be appreciated.
Thanks,
AP
|
| 2189 |
If you do not like having to select an area first, here is something
slightly different. 'Select A1:G1, and run "EnterData" You will now be
limited to these columns. For me, there is less chance overwriting data in
other column.
Sub EnterData()
ActiveSheet.ScrollArea = Selection.EntireColumn.Address
With Application
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToRight
End With
' If you have Excel XP
Application.Speech.SpeakCellOnEnter = True
End Sub
Sub EnterDataOff()
ActiveSheet.ScrollArea = vbNullString
With Application
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlDown '<-Your defalut
End With
Application.Speech.SpeakCellOnEnter = False
End Sub
= = = = = = = = = = = = = = = =
Just for fun...my computer is pretty obnoxious with me now. So, what's a
few more...<vbg
Sub EnterData()
Dim v As Variant
Dim s As String
v = Selection.EntireColumn.Address
ActiveSheet.ScrollArea = v
With Application
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToRight
End With
' If you have Excel XP
If CLng(Application.Version) = 10 Then
Application.Speech.SpeakCellOnEnter = True
s = "Hello #. I will limit your input to columns ## . thru ###"
v = Replace(v, "$", vbNullString)
v = Split(v, ":")
s = Replace(s, "###", v(1))
s = Replace(s, "##", v(0))
s = Replace(s, "#", Application.UserName)
Application.Speech.Speak s
End If
End Sub
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"DrinkCabSav" <To reply remove XXX] victoryXXX@subdimension.com wrote in
message news:agmct8$snf$1@lust.ihug.co.nz...
Hi,
Have found how to make the next cell active to the right when I hit enter,
now I wanna make it go to the next cell below and hard left.
Eg. Say enter data in cell A1, hit enter it moves to B1 and so on... Then
in
cell G1 when I hit enter I want the cursor in cell A2.
How do I set Excel up to do it?
TIA
--
==============================
Can't afford to be a philanthropist anyway!
==============================
|
| 2219 |
Andonny
What you are displaying is the serial number for the date, (based on 1st Jan
1900, day 1). If you wrap the TODAY function in the TEXT one it will work
="Date: " & TEXT(TODAY(),"dd/mm/yyyy")
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Andonny" <wje@multiline.com.au wrote in message
news:OdwLtctKCHA.1624@tkmsftngp10...
Hi again,
I also tried this ="Date: " & TODAY() and it gave me Date: 37451 . I
think this answered my previous question but I still don't know how to get
a
proper date displayed using the combinations of text and date formated
cells.
Thanks a million
Andonny
Andonny <wje@multiline.com.au wrote in message
news:euDu8RtKCHA.2324@tkmsftngp13...
Hi,
I would like to combine text and cells into one cell.
Example:
I would like to have "Week: 1/7/2002 to 8/7/2002" in Cell A1 but it
should
be put together from other cells like
Week: B5 to B6. The first date should come from cell B5 and the
second
date from cell B6 and the text "Week:" and "to" should already be in
cell
A1.
Is this achieveable?
Thanks for your help
Andonny
|
| 2225 |
Hi Andonny,
In ThisWorkBook:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
You can find the above from within ThisWorkBook where
you will see two dropdowns the lefthand one has
(General) or Workbook choose Workbook
the righthand dropdown includes BeforePrint.
--for others seeking information--
More information on Event Macros in
/dmcritchie/excel/event.htm
More information on pathname in footers in
/dmcritchie/excel/pathname.htm
Excel XP users can place full pathname into the footer
without a macro. (In VBA this is entered as &Z)
&[Path]&[File] inserts the path and file name.
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Andonny" <wje@multiline.com.au wrote in message news:#2xtS3xKCHA.388@tkmsftngp09...
Hi,
I would like to achieve that whenever I print an excel sheet it has the path
in the footer. I know about the code placed into ThisWorkbook like
Sub UpdateFooter()
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
End Sub
Is it possible to have something in personal.xls or somewhere that I don't
have to write the code into every workbook.
Thanks for your help
Andonny
|
| 2259 |
Using Excel XP:
Is there a way to enter a fraction of a second?
I'm very new at Excel, so feel free to talk down to me.
What I want to do is keep track of my jogging times between way
points. It's probably anal, but I would like to enter my stopwatch
times Including hundredths of a second.
As near as I seem to be able to get is using the time format that
only allows hours, minutes, and seconds (entered as 00:00:00). I
could not find a format that would allow 00:00:00.00.
Thanks,
- Dick
|
| 2261 |
Use a period when entering 10th & 100th (00:00:00.00)
use custom format (formatcellnumberscustom)
hh:mm:ss.00
so my personal marathon record would be entered as
2:56:18.20
<g
--
Regards,
Peo Sjoblom
"Dick" <knot_on_yer@life.com wrote in message
news:e8Fc4i3KCHA.1916@tkmsftngp11...
Using Excel XP:
Is there a way to enter a fraction of a second?
I'm very new at Excel, so feel free to talk down to me.
What I want to do is keep track of my jogging times between way
points. It's probably anal, but I would like to enter my stopwatch
times Including hundredths of a second.
As near as I seem to be able to get is using the time format that
only allows hours, minutes, and seconds (entered as 00:00:00). I
could not find a format that would allow 00:00:00.00.
Thanks,
- Dick
|
| 2423 |
I am running Excel macros that copy and paste data (lots
of copy and paste) into an other workbook. Using XL97,
this macro took 7 sec. to execute with a Pentium3 128Mb
ram. Now with a Pentium 4 256Mb ram it takes at least 50
sec. for Excel XP to execute the same macro. I have tried
many other macros and the result is always the same, XP
much slower even with more powerfull PC (these macros have
Application.Calculation = xlManual and screen updating
turned off).
Does anyone know a solution to this problem?
Thanks
Eric
|
| 2427 |
Are you accessing any print options in the Macro?
"elliot" <derybosi@noos.fr wrote in message
news:1988e01c22d1a$ed089f00$3bef2ecf@TKMSFTNGXA10...
I am running Excel macros that copy and paste data (lots
of copy and paste) into an other workbook. Using XL97,
this macro took 7 sec. to execute with a Pentium3 128Mb
ram. Now with a Pentium 4 256Mb ram it takes at least 50
sec. for Excel XP to execute the same macro. I have tried
many other macros and the result is always the same, XP
much slower even with more powerfull PC (these macros have
Application.Calculation = xlManual and screen updating
turned off).
Does anyone know a solution to this problem?
Thanks
Eric
|
| 2428 |
no, it is only coping and pasting, no formatting or
printing involved. And the Task Pane Clipboard is
disactivated
-----Original Message-----
Are you accessing any print options in the Macro?
"elliot" <derybosi@noos.fr wrote in message
news:1988e01c22d1a$ed089f00$3bef2ecf@TKMSFTNGXA10...
I am running Excel macros that copy and paste data (lots
of copy and paste) into an other workbook. Using XL97,
this macro took 7 sec. to execute with a Pentium3 128Mb
ram. Now with a Pentium 4 256Mb ram it takes at least 50
sec. for Excel XP to execute the same macro. I have
tried
many other macros and the result is always the same, XP
much slower even with more powerfull PC (these macros
have
Application.Calculation = xlManual and screen updating
turned off).
Does anyone know a solution to this problem?
Thanks
Eric
.
|
| 2429 |
Microsoft has an excellent how to guide on its web site for new Word XP
users, it is advertised for Legal users but applies to anyone. It can
be down loaded by anyone who wants a copy.
Does anyone know of a similar document for Excel XP, not necessarily at
the Microsoft website?
Thanks.
|
| 2442 |
I would imagine that if you gave the name for the Word XP
document something similar might be found for Excel XP
nearby.
You can find some off-site tutorials on my excel.htm page
mostly to university sites and oriented to students. Is there
something of a particular nature that you want to see.
Jumping into the middle of one ...
Lesson 11: Creating a Custom Form
/ExcelDev/Articles/sxs11pt2.htm
or start at the beginning intro to lots of articles on Excel
from the green bar at top of article.
/exceldev/e-a&sa.htm
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Lloyd H. London" <lhlondon@ix.netcom.com wrote in message news:3D34AC1E.9D77590B@ix.netcom.com...
Microsoft has an excellent how to guide on its web site for new Word XP
users, it is advertised for Legal users but applies to anyone. It can
be down loaded by anyone who wants a copy.
Does anyone know of a similar document for Excel XP, not necessarily at
the Microsoft website?
Thanks.
|
| 2458 |
A coworker of mine has two issues with Excel. I am trying
to help her, but this has gone beyond my experience. She
is trying to figure out how to put a benchmark in a chart
in Excel XP. Help, in this case, is no help at all, and we
haven't found anything in the knowledge base either.
SHe would also like to be able to show a line for standard
deviations on the chart. I have created the formula to
obtain standard deviations in her worksheet, but now we
are unsure how to get it into the bar chart.
Any ideas on these two issues would be greatly
appreicated. If you can, please post here and email to me
a baldwin@nahealth.com. Thanks in advance!
|
| 2464 |
I'm not sure if this will help but give this a go...
Select the Std Dev data and drag it into the barchart... this will create
another set of bars on the chart. If you RIGHT click on the Std Dev Bars and
select Chart type, line graph. It will change the Std deviation bars into a
line chart, leaving your original data as a bar chart.
Please note, you may need to create 2 separate standard deviation sets of
data if you want the effect of :
Original data +/- Std Dev.
You will need to create one column that has the formula
=Original Data+Std Dev
and another
=Original Data - Std Dev
You will need to drag both sets of data into the bar chart.
As for the benchmark, you can use a similar technique, by creating another
column/row which parallels the original data with the magic benchmark
number.
eg
Orig Data BenchMark
10 75
20 75
40 75
20 75
30 75
Drag that data into the chart, and change it to a line chart (same as what
was done with the Std Dev bars)
Regards
Scott
"Nikki Baldwin" <baldwin@nahealth.com wrote in message
news:18a6601c22d36$0a408680$b1e62ecf@tkmsftngxa04...
A coworker of mine has two issues with Excel. I am trying
to help her, but this has gone beyond my experience. She
is trying to figure out how to put a benchmark in a chart
in Excel XP. Help, in this case, is no help at all, and we
haven't found anything in the knowledge base either.
SHe would also like to be able to show a line for standard
deviations on the chart. I have created the formula to
obtain standard deviations in her worksheet, but now we
are unsure how to get it into the bar chart.
Any ideas on these two issues would be greatly
appreicated. If you can, please post here and email to me
a baldwin@nahealth.com. Thanks in advance!
|
| 2550 |
I don't think you can disable it, but if you enter a single quote then your
value, xl will treat it as text.
'A01-39
or you can format the cell as text before you start typing.
(It's kind of the same irritation if you want to show 01-03 and excel changes it
to January 3rd.)
Smokin wrote:
how do i disable this hijdri date (Islamic calendar)
function?
-----Original Message-----
It is a hijdri date. I don't know what that is, but this
is the explanation
I once saw.
--
Regards,
Niek Otten
Microsoft MVP - Excel
"smokin" <sck@mail.med.upenn.edu wrote in message
news:19c8e01c22da0$7bd59f50$3bef2ecf@TKMSFTNGXA10...
I don't know why but when you enter A01-39 in a cell in
Excel XP (previous version of Excel work fine) you get
back 39 and then some arabic characters... in the cell
definition box (above spreadsheet it lists a date
something like 2/2/2007).
Is this A01 some type of date function in Excel XP?
Any help would be greatly appreciated.
Thanks.
.
--
Dave Peterson
ec35720@msn.com
|
| 2553 |
Tim,
Excel XP allows you to specify that users can (or can't) do sorts,
autofilters, and all kinds of things when the sheet is protected.
Unfortunately, it doesn't seem to include showing and hiding rows using the
outline symbols, which is a bit ironic, since this isn't changing any data
any more than a sort or autofilter, only visibility.
If no one comes along with a better solution, and if you're showing and
hiding entire levels (using the numbers at the top of the outline symbols,
rather than individual groups), you can make macros like:
Sub setup()
ActiveSheet.Protect userinterfaceonly:=True
End Sub
This protects the sheet, but not from the ravages of a macro. It only has
to be run once (and will last through closing and reopening of the workbook
Now have a couple or three of:
Sub Level1
ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub
Sub Level2
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
They go in a regular module. Now use the drawing toolbar to put buttons on
the sheet, then use the right click to assign each to its respective macro.
Be sure to put the buttons where they won't get squashed by the hiding of
the outline section. Now those buttons can work the showing and hiding of
the outline while the sheet is protected.
Regards from Virginia Beach,
EarlK
-------------------------------------------------------------
"Tim McPhillips" <mcptjm@yahoo.com wrote in message
news:197ca01c22dc6$101c4d60$19ef2ecf@tkmsftngxa01...
I need help with some forecasting templates I am creating
at work. I have worksheets that have three levels of
outlines in the rows (to expand and contract detail). I
would like to prevent the user from overwriting certain
cells by locking cells and protecting the sheet. However,
if I protect the sheet the outlines become inoperable. My
company is on the cutting edge of technology with Excel 97
and I don't know if this problem has been addressed in
later versions of Excel. Any suggested work-arounds? Any
help would be appreciated.
Tim McPhillips
|
| 2622 |
Hi Dev,
For Excel 2000 and all Excel before Excel 2002 you can use custom number format
[9999999.99]##\,##\,##\,##0.00;[99999.99]"##\,##\,##0.00;"##,##0.00
This format separates groups for India/Thailand, format valid for positive numbers up to 99,99,99,999.99 or 999,999,999.99) in
Excel XP see function BhatText as in Thai Bhat currency.
The help information for Excel 2002, but everyone using file would have to have Excel 2002 so there would be no advantage if
exchanging files.
BAHTTEXT
/assistance/offhelp/offxp/excel/xlmain10/html/Bahttext.htm
Format numbers using Thai digits
/assistance/offhelp/offxp/excel/xlmain10/html/xlhowsadisplaythainumbers.htm
Convert Arabic numbers to Thai text format
/assistance/offhelp/offxp/excel/xlmain10/html/xlhowsaenterthaifunctionformulassheets.htm
Use Thai numbers and dates in headers and footers
/assistance/offhelp/offxp/excel/xlmain10/html/xlhowsausethainumbersheadersfooters.htm
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Dev" <dev9812@yahoo.com wrote in message news:b4ec7768.0207180302.2828b6f6@posting.google.com...
Hi,
I have a query relating to the number format that we use in Excel.
Iam using Excel 2000. I need the number format in this style. For
Ex.when I type 1,50,000 in the Excel worksheet it appears like this :
150,000.
But, I want my numbers like this ONLY : 1,50,000. Is there a way to
accomplish the above.
Likewise if I enter a number in crores (say, for example One crore
and fifty lacs) it should appear in worksheet like this
Rs.1,50,00,000.
Awaiting very eagerly for your sugestions ..
Rgs,
Dev
India.
|
| 2623 |
I have a user having a problem with Excel hanging when he changes the
margins in print setup. His computer is running Win2000 SP2 and Excel XP.
Any ideas?
Thanks!
--
Michael Roth
Emerald Isle Forge
www.emeraldisleforge.netfirms.com
|
| 2672 |
Nikki -
I have a technique I often use for this task. I make the column chart (or a
line chart as run chart, whatever), then add a series with the Y values I
want, along the right hand edge of the chart. I add negative horizontal
error bars to this new series as the benchmark or control limits.
Suppose I want to put lines for Mean + 3 Sigma, Mean, and Mean - 3 Sigma on
the chart. I set up the following in a stray area of the chart, where Range
is the worksheet range containing the data we're analyzing:
+3S 1 =AVERAGE(Range)+3*STDEV(Range)
Mean 1 =AVERAGE(Range)
-3S 1 =AVERAGE(Range)-3*STDEV(Range)
I select the second and third columns of this range, copy it, select the
chart, go to Edit menu Paste Special, and add this as a new series,
categories in first column.
Of course Excel adds this series in the same style (column or line) as the
rest of the series. Right click on the new series, choose Chart Type, and
pick the XY Scatter style with markers but no line.
Excel has put a secondary X and secondary Y axis onto the chart. Select the
secondary Y axis (right hand edge), and press Delete. Double click the
secondary X axis (top of chart), on the Scale tab, set Min=0 and Max=1, and
on the Patterns tab, select None for Major and Minor Tick Marks and Tick
Labels. This new series is now aligned on the right edge of the chart where
the secondary Y axis had been.
Double click the new series, and on the X Error Bars tab, click the Minus
box, and enter 1 for Fixed Value. This produces horizontal lines that span
the plot area.
Finally add the labels in the first column we added above. You can do it
manually, but I'd suggest you download Rob Bovey's free XY Chart Labeler from
When you install this, it adds a Chart Labels
command to the Tools menu. It's as intuitive as any of Excel's built in
tools. Anyway, select the chart and do Tools Chart Labels Add Labels.
Select the new series, then select the range with the labels, then align the
labels to the right of the points. You will probably have to drag the right
edge of the plot area to the left to allow enough room for these labels. If
you want, you can disappear these points by double clicking on them, and
picking no line and no markers from the Patterns tab.
You can add another point for the benchmark, or you can add it as another
one-point series. You have to format all error bars on a series the same
way, so if you want red statistics lines and a blue benchmark line, you'll
need two added series.
This seems like a long drawn out process, but after you've done it a few
times and become familiar with the steps, it isn't bad at all. I set up a
PowerPoint slide with four of these today in about 10 minutes.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
/jonpeltier/Excel/index.html
_______
In article <18a6601c22d36$0a408680$b1e62ecf@tkmsftngxa04, Nikki Baldwin
said...
A coworker of mine has two issues with Excel. I am trying
to help her, but this has gone beyond my experience. She
is trying to figure out how to put a benchmark in a chart
in Excel XP. Help, in this case, is no help at all, and we
haven't found anything in the knowledge base either.
SHe would also like to be able to show a line for standard
deviations on the chart. I have created the formula to
obtain standard deviations in her worksheet, but now we
are unsure how to get it into the bar chart.
Any ideas on these two issues would be greatly
appreicated. If you can, please post here and email to me
a baldwin@nahealth.com. Thanks in advance!
|
| 2749 |
Keshava
How about
Sub SumSelection()
MsgBox Application.Sum(Selection)
End Sub
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Keshava Prashanth" <keshav@analytica-india.com wrote in message
news:17a9101c22fce$6ad3a960$a5e62ecf@tkmsftngxa07...
Hi,
I should display the sum of all cells for the column where
the cell is highlighted. Using VBA how can I achieve this.
Please help me.
Regards,
Keshav.
|
| 2852 |
Thanks to you both, Scott and Jon, for the ideas. I have=20
passed them along to my coworker.
Nikki
In article <18a6601c22d36$0a408680
$b1e62ecf@tkmsftngxa04, Nikki Baldwin=20
said...
A coworker of mine has two issues with Excel. I am=20
trying=20
to help her, but this has gone beyond my experience. She=20
is trying to figure out how to put a benchmark in a=20
chart=20
in Excel XP. Help, in this case, is no help at all, and=20
we=20
haven't found anything in the knowledge base either.
SHe would also like to be able to show a line for=20
standard=20
deviations on the chart. I have cr=CD
{=9Dw=C0=1F"=04D=F6=DEv\=07=DDv=04t=FC=08 =EC=02=02eated the formula to=20
obtain standard deviations in her worksheet, but now we=20
are unsure how to get it into the bar chart.
Any ideas on these two issues would be greatly=20
appreicated. If you can, please post here and email to=20
me=20
a baldwin@nahealth.com. Thanks in advanc
|
| 3572 |
I am also using XL XP. I'm not sure if it impacts things
regarding this issue, but the WorkbookBeforeClose event is
an application-level event handler. See below
In class Module:
Public Withevents appevent as Application
Regular Module:
Private Sub appevent_WindowResize(ByVal Wb As
Excel.Workbook, _
ByVal Wn As Excel.Window)
...
filename = Application.GetSaveAsFilename()
....
End Sub
The application-level event handler appears to working in
all other areas except the GetSaveAsFilename. Not sure if
this matters or not, but I figured I would mnention it.
Thanks.
-----Original Message-----
Erik
Tried it in XL XP and it works fine (Code below)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
filename=Application.GetSaveAsFileName()
MsgBox filename
End Sub
What version of Xl and do you have other code other than
something simple as
above?
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Erik Wadehn" <Erik_Wadehn@hotmail.msn.com wrote in
message
news:2de301c237f7$5c853a00$9ae62ecf@tkmsftngxa02...
I'm using GetSaveAsFilename in the WorkbookBeforeClose
event using the following statement.
filename = Application.GetSaveAsFilename()
The SaveAs dialog box appears and operates as expected.
However, the file name selected or entered in the dialog
box is not passed back to the "filename" variable above.
The GetSaveAsFilename always passes back an Empty
string.
Has anyone encountered this issue and know what may be
causing it. Thanks.
.
|
| 3575 |
Try grouping all your defined names under one name. For
example:
Name Range
one =Sheet1!$C$4:$D$19
two =Sheet1!$G$16,Sheet1!$H$18,Sheet1!$I$21
three =Sheet1!$G$2:$N$4,Sheet1!$K$5,Sheet1!$L$5
four =one,two,three
Go to the Name Box and type in "four" (no quotes) and
press enter to select all the cells.
HTH
Jason
Atlanta, GA
-----Original Message-----
Hi folks
Excel XP, W2K, 256 Mb RAM, 400 Mhz cpu
I need to select a number of groups of cells in one go by
naming them. I
seem to hit a limit when doing the Ctrl/click/drag
process. Does anyone know
if there is a limit and if so what it is. Even better,
does anyone know of a
way round it?
TIA
Mike Boardman
.
|
| 3577 |
I forgot to say that you should break your currently
defined names into smaller groups, then group them all
under 1 name.
Jason
-----Original Message-----
Try grouping all your defined names under one name. For
example:
Name Range
one =Sheet1!$C$4:$D$19
two =Sheet1!$G$16,Sheet1!$H$18,Sheet1!$I$21
three =Sheet1!$G$2:$N$4,Sheet1!$K$5,Sheet1!$L$5
four =one,two,three
Go to the Name Box and type in "four" (no quotes) and
press enter to select all the cells.
HTH
Jason
Atlanta, GA
-----Original Message-----
Hi folks
Excel XP, W2K, 256 Mb RAM, 400 Mhz cpu
I need to select a number of groups of cells in one go
by
naming them. I
seem to hit a limit when doing the Ctrl/click/drag
process. Does anyone know
if there is a limit and if so what it is. Even better,
does anyone know of a
way round it?
TIA
Mike Boardman
.
.
|
| 3627 |
Actually, xl2002 will accept apostrophe's in the worksheet name as long as it's
not the first character.
June'99
is ok
'99
is not
I don't know of a converter that will do the work for you, but maybe you could
run this in xl2k.
It'll loop through a given directory (C:\temp in the code) and try to change the
name of the worksheets.
If it has trouble (can't rename because the workbook is protected, or the an
existing worksheet already has that new name), it'll give you a message box.
Make a note and just do those manually. (Shouldn't be many that need this--a
guess!)
Option Explicit
Sub testme()
Dim otherWkbk As Workbook
Dim Wks As Worksheet
Dim newName As String
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "c:\Temp" 'folder to use
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Application.EnableEvents = False
Set otherWkbk = Workbooks.Open(.FoundFiles(i))
Application.EnableEvents = True
For Each Wks In otherWkbk.Worksheets
newName = Wks.Name
newName = Replace(newName, "'", "_")
'or
'If Left(newName, 1) = "'" Then
' newName = "_" & Mid(newName, 2, Len(newName) - 1)
'End If
On Error Resume Next
Wks.Name = newName
If Err.Number 0 Then
'couldn't rename
MsgBox "Please take a note: " & Wks.Parent.FullName _
& vbLf & Wks.Name & " must be renamed manually."
End If
On Error Resume Next
Next Wks
otherWkbk.Close savechanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub
I commented out the portion that does just the first character. Also, this
needs at least xl2k. It uses Replace (and Replace was added with xl2k). If by
chance you're running xl97, change
newName = Replace(newName, "'", "_")
to
newName = application.substitute(newName, "'", "_")
=======
You may want to try it against a few files in a test folder, too.
=======
As an afterthought, maybe there's something in 123 that can do the same kind of
thing. It might be better/easier putting the solution there--but I haven't used
123 since the DOS days of 123.
Chick Thompson wrote:
Help,
I have end-users that are saving their 123 files with an '
Apostrophe in the tab of 123 then saving it as a ".xls.
When the next user tries to open the file in Excel Xp it
Supposedly is corrupted but I am able to open this in
Excel 2000. In Excel 2000 the worksheet shows the
Apostrophe, in which the end-user does think the file is
corrupted until the person with XP Excel opens it.I know
Excel doesn't like the ' in the tab/worksheet.Is there a
convertor that will open these with out giving an error?
I would not like to go to each spreadsheet and remove
these
Thanks In Advance
--
Dave Peterson
ec35720@msn.com
|
| 3630 |
(a) Your example, which uses _WindowResize, is inconsistent with your
stated requirement that you are using _WorkbookBeforeClose.
(b) Also, I've always believed that event procedures have to be in the
class module, not in a standard module. Is XL really calling the
_WindowResize procedure? Heck, I'm surprised it even compiles
correctly!
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <32a901c23803$07cbcf90$3bef2ecf@TKMSFTNGXA10, Erik
<Erik_Wadehn@hotmail.msn.com wrote
I am also using XL XP. I'm not sure if it impacts things
regarding this issue, but the WorkbookBeforeClose event is
an application-level event handler. See below
In class Module:
Public Withevents appevent as Application
Regular Module:
Private Sub appevent_WindowResize(ByVal Wb As
Excel.Workbook, _
ByVal Wn As Excel.Window)
...
filename = Application.GetSaveAsFilename()
....
End Sub
The application-level event handler appears to working in
all other areas except the GetSaveAsFilename. Not sure if
this matters or not, but I figured I would mnention it.
Thanks.
-----Original Message-----
Erik
Tried it in XL XP and it works fine (Code below)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
filename=Application.GetSaveAsFileName()
MsgBox filename
End Sub
What version of Xl and do you have other code other than
something simple as
above?
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Erik Wadehn" <Erik_Wadehn@hotmail.msn.com wrote in
message
news:2de301c237f7$5c853a00$9ae62ecf@tkmsftngxa02...
I'm using GetSaveAsFilename in the WorkbookBeforeClose
event using the following statement.
filename = Application.GetSaveAsFilename()
The SaveAs dialog box appears and operates as expected.
However, the file name selected or entered in the dialog
box is not passed back to the "filename" variable above.
The GetSaveAsFilename always passes back an Empty
string.
Has anyone encountered this issue and know what may be
causing it. Thanks.
.
|
| 3634 |
Sorry...that was a typo on my end. You are correct that
both the:
Public WithEvents appevent As Application
and
Private Sub appevent_WorkbookBeforeClose(ByVal Wb As
Workbook, Cancel As Boolean)
are in the Class module. I am instiating the component
with
Sub AppEvents_InitRoutine()
Set eventNew.appevent = Application
End Sub
which is in a regular module.
The application event handler is working for everything
except the GetSaveAsFilename. Any thoughts what might be
going on? Thanks.
-----Original Message-----
(a) Your example, which uses _WindowResize, is
inconsistent with your
stated requirement that you are using
_WorkbookBeforeClose.
(b) Also, I've always believed that event procedures
have to be in the
class module, not in a standard module. Is XL really
calling the
_WindowResize procedure? Heck, I'm surprised it even
compiles
correctly!
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <32a901c23803$07cbcf90$3bef2ecf@TKMSFTNGXA10, Erik
<Erik_Wadehn@hotmail.msn.com wrote
I am also using XL XP. I'm not sure if it impacts
things
regarding this issue, but the WorkbookBeforeClose
event is
an application-level event handler. See below
In class Module:
Public Withevents appevent as Application
Regular Module:
Private Sub appevent_WindowResize(ByVal Wb As
Excel.Workbook, _
ByVal Wn As Excel.Window)
...
filename = Application.GetSaveAsFilename()
....
End Sub
The application-level event handler appears to working
in
all other areas except the GetSaveAsFilename. Not
sure if
this matters or not, but I figured I would mnention
it.
Thanks.
-----Original Message-----
Erik
Tried it in XL XP and it works fine (Code below)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
filename=Application.GetSaveAsFileName()
MsgBox filename
End Sub
What version of Xl and do you have other code other
than
something simple as
above?
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Erik Wadehn" <Erik_Wadehn@hotmail.msn.com wrote in
message
news:2de301c237f7$5c853a00$9ae62ecf@tkmsftngxa02...
I'm using GetSaveAsFilename in the
WorkbookBeforeClose
event using the following statement.
filename = Application.GetSaveAsFilename()
The SaveAs dialog box appears and operates as
expected.
However, the file name selected or entered in the
dialog
box is not passed back to the "filename" variable
above.
The GetSaveAsFilename always passes back an Empty
string.
Has anyone encountered this issue and know what may
be
causing it. Thanks.
.
.
|
| 3637 |
I like Jason's answer, but another option is to use a little VBA.
The length limit appears to be a problem inside excel--not vba.
But you'll have to either come up with the range or use something that helps
determine if the cell is part of the range.
(I have one macro that loops through a column. Checks it background color--if
grey, it includes the cell to its right in the range that I want. (Used for
data validity. Cells next to grey cells should be 0/empty for certain
customers.) Well, I thought it was pretty neat.)
Mike Boardman wrote:
Hi folks
Excel XP, W2K, 256 Mb RAM, 400 Mhz cpu
I need to select a number of groups of cells in one go by naming them. I
seem to hit a limit when doing the Ctrl/click/drag process. Does anyone know
if there is a limit and if so what it is. Even better, does anyone know of a
way round it?
TIA
Mike Boardman
--
Dave Peterson
ec35720@msn.com
|
| 3653 |
My general rule is to always put in a Application.EnableEvents=False at
the start of every event procedure. Works more often than harms.
Also, what happens if you put in a breakpoint just before the
GetSaveAsFilename and then step through the code. What is the value of
filename? How is filename declared?
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <33de01c23824$60a74bf0$37ef2ecf@TKMSFTNGXA13, Erik
<Erik_Wadehn@hotmail.msn.com wrote
Sorry...that was a typo on my end. You are correct that
both the:
Public WithEvents appevent As Application
and
Private Sub appevent_WorkbookBeforeClose(ByVal Wb As
Workbook, Cancel As Boolean)
are in the Class module. I am instiating the component
with
Sub AppEvents_InitRoutine()
Set eventNew.appevent = Application
End Sub
which is in a regular module.
The application event handler is working for everything
except the GetSaveAsFilename. Any thoughts what might be
going on? Thanks.
-----Original Message-----
(a) Your example, which uses _WindowResize, is
inconsistent with your
stated requirement that you are using
_WorkbookBeforeClose.
(b) Also, I've always believed that event procedures
have to be in the
class module, not in a standard module. Is XL really
calling the
_WindowResize procedure? Heck, I'm surprised it even
compiles
correctly!
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Microsoft MVP -- Excel
--
In <32a901c23803$07cbcf90$3bef2ecf@TKMSFTNGXA10, Erik
<Erik_Wadehn@hotmail.msn.com wrote
I am also using XL XP. I'm not sure if it impacts
things
regarding this issue, but the WorkbookBeforeClose
event is
an application-level event handler. See below
In class Module:
Public Withevents appevent as Application
Regular Module:
Private Sub appevent_WindowResize(ByVal Wb As
Excel.Workbook, _
ByVal Wn As Excel.Window)
...
filename = Application.GetSaveAsFilename()
....
End Sub
The application-level event handler appears to working
in
all other areas except the GetSaveAsFilename. Not
sure if
this matters or not, but I figured I would mnention
it.
Thanks.
-----Original Message-----
Erik
Tried it in XL XP and it works fine (Code below)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
filename=Application.GetSaveAsFileName()
MsgBox filename
End Sub
What version of Xl and do you have other code other
than
something simple as
above?
--
HTH
Nick Hodge
Southampton, England
Microsoft MVP - Excel
nick_hodge@btinternet.com
Excel XP\WinXP
"Erik Wadehn" <Erik_Wadehn@hotmail.msn.com wrote in
message
news:2de301c237f7$5c853a00$9ae62ecf@tkmsftngxa02...
I'm using GetSaveAsFilename in the
WorkbookBeforeClose
event using the following statement.
filename = Application.GetSaveAsFilename()
The SaveAs dialog box appears and operates as
expected.
However, the file name selected or entered in the
dialog
box is not passed back to the "filename" variable
above.
The GetSaveAsFilename always passes back an Empty
string.
Has anyone encountered this issue and know what may
be
causing it. Thanks.
.
.
|
| 3770 |
Nice ideas...
"Dana DeLouis" <ng_only@hotmail.com wrote in message
news:#REEsJ#NCHA.2708@tkmsftngp13...
If you are interested, you might be able to write a macro to sort Blanks
to
the Top.
Here is a quick conversion of something that I use. I removed lines from
the code that work with formulas.
You can play with it to get it to do what you want.
This assumes the data is Text (No Formulas), and that you have a Heading
Row
at the Top of your Sort range.
This Sorts A-Z, with Blanks on Top.
The reason for "Areas.Count" is that if you do have Blanks, then you Will
have a heading row (First Area), Blanks, then your data (Second Area)
The Area here will not have a Heading Row. If there are no blanks, then
there will be 1 area, "With" a heading Row.
Again, you can be as creative as you want here. This is just a general
idea
to play with.
Another idea in addition to David's for Undoing a VBA Sort is to have VBA
turn on Track Changes prior to VBA sorting your data. It works pretty
well
in Excel XP.
Some do not like this idea because the Workbook becomes "Shared" while
tracking changes. For home use, I think it's a pretty neat way to "Undo"
whatever VBA might have done to your workbook.
Sub SortAZBlanksOnTop()
' Dana DeLouis
' Sort Ascending, Blanks on Top
On Error Resume Next
With Selection
.SpecialCells(xlCellTypeBlanks) = "#N/A"
.Sort _
Key1:=.Cells(1), _
Order1:=xlDescending, _
Header:=xlYes
.SpecialCells(xlCellTypeConstants, 16).ClearContents
With .SpecialCells(xlCellTypeConstants, 7)
If .Areas.Count = 1 Then
.Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlYes
Else
.Areas(.Areas.Count).Sort _
Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlNo
End If
End With
End With
End Sub
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Tim Zych" <tzych@earthlink.net wrote in message
news:OXOslq9NCHA.2224@tkmsftngp09...
Hi Dave:
I agree that it's useful for Excel. I am wondering if there is a reason
that
goes to the core of the way Excel is designed, or rather is this the
"Gee,
Microsoft's tests confirm that users prefer this" sort of reason.
Btw, StarCalc does the same thing.
Why doesn't Access offer the same "convenience"? Or Word?
Just curious.
"" <dmcritchie@msn.com wrote in message
news:eoZaEu8NCHA.2488@tkmsftngp09...
Hi Tim,
Empty Cells, and cells with blanks sorting below
other data sure is helpful compared to having them
bunched up at the front. The following information
is from or draws from my page:
Sorting, Some notes on sorting in Excel
/dmcritchie/excel/sorting.htm
Excel modifies the ASCII sorting sequence anyway.
Numbers are sorted from the smallest negative number
to the largest positive number, followed by Text, and text
that includes numbers.
Text is sorted in this order, with lowercase being
equivalent to uppercase.
Actually Microsoft Excel help indicates the following order,
which is not the ASCII collating sequence, doesn't include
all of the 128 characters, and is not exactly match my
experience for sorting text (try for singles and multiples):
0 1 2 3 4 5 6 7 8 9 ' - (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ]
^
_
`
{ | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
In logical values, FALSE is sorted before TRUE.
All error values are equal.
Blanks are always sorted last. -OR- so it says .
HTH,
, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: /dmcritchie/excel/excel.htm
Search Page: /dmcritchie/excel/search.htm
"Tim Zych" <tzych@earthlink.net wrote in message
news:Of3xA03NCHA.1624@tkmsftngp10...
Why are blanks in Excel placed last in a sort, while in Access and
Word
they
are placed first?
Thanks.
Tim
|
| 3825 |
Friends,
In Excel XP, when you start the program there will appear a panel on the
left side showing recent documents etc. etc.
When I installed XP it was there. Recent times it is not appearing.
Can someone guide me how to make it appear again?
Regards
Sam
/group/world-of-books
|
| 3902 |
Hi Sam,
I believe you are talking about the "Task Pane" in Excel
version 2002.
Click on "View" in the top toolbar, then click "Task
Pane." You can move this around as you see fit.
I hope this answers your question.
Ryan
-----Original Message-----
Friends,
In Excel XP, when you start the program there will
appear a panel on the
left side showing recent documents etc. etc.
When I installed XP it was there. Recent times it is
not appearing.
Can someone guide me how to make it appear again?
Regards
Sam
/group/world-of-books
.
|
| 4045 |
This is posting 9 of Frequently Asked Questions for the Excel newsgroups
microsoft.public.excel.misc, microsoft.public.excel.programming and
microsoft.public.excel.worksheet.functions. Topics are:
The Excel application and Excel files
Worksheet functions and formats
Dates and times
Macros, VBA functions
I didn't find my answer here, now what ?
Good resources on the web
Collected by Harald Staff, Microsoft Excel MVP.
This FAQ can also be found at Debra Dalgleish's website
/xlfaqIndex.html -it's worth a bookmark.
If you reply to this posting, reply to a single group and quote as little as
possible.
*************************************************
**** The Excel application and Excel files ****
* When I start Excel, why do a million files open up automatically?
Menu Tools Options General has an entry for "Startup directory", and all
files there will be loaded when Excel starts. Alter or remove this entry.
* When I start XL, I get the error message "Compile error in Hidden Module"
An add-in with a programming error is bothering you.
1 Tools, Add-ins
2 note which are checked
3 uncheck all (but one)
4 restart XL
5 if no error, check the next one and repeat from step 4
Got the error? uncheck the one you last checked.
If no luck, see if there's an .xla file in the startup directory (see
previous tip). Scanner software typically place itself there, sometimes with
code errors like the one in question.
Not an add-in? It will be a hidden workbook then.
1 Try (in Excel) Windows, Unhide and write down which
files are listed.
2 Unhide them.
3 Now go to the VBE (press Alt-F11).
4 On the left side there should be the project explorer.
5 Select the first project you unhid
6 From the menu choose Debug, compile
7 No Errors? select the next project listed, repeat 6.
* Why do the column headers show numbers instead of letters? How do I change
my column headings so they are back to alpha letters instead of numbers ?
Tools / Options / General / Settings / R1C1 Reference Style (uncheck)
* How can I change the color of the sheet tabs?
Excel 10 (XP) is the only version that can color sheet tabs.
* I want to lock in my Title Row and keep it there Visible while I scroll
down to see the rest of my data. Any ideas?
Assuming title row is 1, select A2 then WindowFreeze Panes.
* I need more than 256 columns and/or 65536 Rows.
Excel has no more. Quattro Pro v9 has 1 million rows and 18278 columns.
* I just began to design an intricate spreadsheet and after over an hour got
an error message in Excel - which then closed automatically. Unfortunately,
I did not save my file. Excel did not automatically recover the file. Is
there anything I can do to get it back?
No. There should be an Autosave add-in in Tools Addin menu that you now
might consider start using. (But then again, you may not want to overwrite
an existing file with every little test you do in it, so be careful). Jan
Karel Pieterse has an add-in Autosafe.zip downloadable from
www.bmsltd.co.uk/mvp. See also 's
/dmcritchie/excel/backup.htm for more on backup and
recovery.
Finally, Excel XP has great backup and recovery tools, so upgrading is a
good future solution to those problems.
* All of a sudden a number of my Excel 2000 files have become "read-only".
Clear out c:\windows\temp directory on the machine that houses the files,
reboot.
* I have an excel file that I use every day at work. Some time ago,
mysteriously, the file began opening two copies of itself every time I
double-click the icon. If I close one of the copies, both close. Any changes
made to one copy show up in the other.
This sounds like you just have two windows open that are displaying the same
workbook. You can close the extra
window by clicking the lower of the two X buttons in the upper right corner
of the screen (don't click the top X button, as that will close Excel). Then
save the workbook.
* When I type a number; example 44 it becomes 0.44 even if I format the cell
without decimals.
Goto Tools/Options/Edit and uncheck the fixed decimals checkbox.
* When I click on an Excel file to open it, the file opens but an error
message is displayed that says a file with that name is already open and
that I can not open two files open with the same name.
If you are sure it's not really happening -you may have Book1 open and then
attempt to open a Book1 from another folder- then try re-register Excel.
click startrun and
"C:\Program Files\Microsoft Office\Office\Excel.Exe" /regserver
(include the quotes)
adapt to fit your path. Other things to look at:
Tools=Option=General Tab, make sure "Ignore Other Applications" is not
checked
* Why does Excel say my file has links, when I know it doesn't?
Links come in several flavors; linked formulas, defined names (Insert Name
Define menu), objects (buttons and stuff) assigned to remote macros, ...
You might find the FINDLINK.XLA program useful - you can get it from Stephen
Bullen's web site:
* Is there a way to allow the use of autofilter on a protected worksheet ?
This needs a macro to run first:
Sub Protect_keep_filter()
With ActiveSheet
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
contents:=True, Scenarios:=True, UserInterfaceOnly:=True
End With
End Sub
Note that the .enableautofilter has to be reset each time you open your
file. (It's not persistent between closes.)
* How many worksheets I can put in a workbook?
It is not a limit per se, but of course there is a practical one which
depends on the computer resources..
* How many Characters can be placed in a Cell?
In Excel 97 and above, you can have up to 32K characters in a worksheet
cell, but only the first 1000 or so can be displayed (the exact number
depends on your font and display characteristics).
* I've lost my password !
Yeah sure. Well, there are passwords and there are passwords. One is to open
the file, one other is to unprotect the spreadsheet, still another is to
unprotect the workbook and yet another one is to unprotect the macro code.
File and VBA passwords can not be cracked by a "normal macro", workbook and
worksheet passwords are fairly easy. A search for "excel password" at
/ will find both commercial and free solutions of
varying quality and brutality.
* Can anyone advice how to protect an Excel file (and associated code) from
un-authorized copying and/or create time limited functionality ?
There is no fool-proof method for this, and most solutions limit themselves
to require macros enabled on opening. This said, creating your own system is
a fun challenge allowing lots of "evil creativity".
* When I try to open XL, it freezes and all I can do is reboot
* Excel crashes on me regularly, what can I do
* EXCEL caused an invalid page fault...
* Illegal Operation Error when starting Excel
To-Do List:
Try opening Excel without any addins or hidden workbooks:
Start, Run,
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Automation
Note you may have to change the path. If your error disappears, there is
either an add-in or a (hidden) workbook that is loaded upon XL's start,
which bothers you.
- Try locating the XLSTART directory, move everything from there.
- In XL: Tools, Addins, note the ones checked and uncheck
them one at the time, each time restarting XL
- In XL, Tools, options, general. Check if a path is
entered after "Alternate startup file location". Clear it.
Another option is to open XL in Safe mode:
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe
Also, you might try:
"C:\Program Files\Microsoft Office\Office\Excel.exe" /Regserver
Yet another possible problem is a corruption of your toolbar customisation
file. Locate all files with extension .xlb and rename the extension(s) to
something like .old .Now try and start XL again.
Finally, this is what MS has to say:
/default.aspx?scid=kb;en-us;Q280504
**** Worksheet functions and formats ****
* How can I protect a formulas from being deleted or changed?
Select all cells that users ARE allowed to change. Go menu Format Cells
Protection and uncheck Locked.
When done, protect the worksheet in menu Tools Protection Protect
worksheet.
* How can I enter the date into a cell so it doesn't change every day?
Press Ctrl ; (that's holding Ctrl down while pressing semicolon.) Ctrl :
will enter the time.
* I have to enter the Expenses and to select from some criteria. That means,
the user can only enter 'DHL', 'FEDEX', 'UPS'. In other word once the user
move into the corresponding cell under expenses it will popup a combo box
with the above 3 Companies and user will select one.
Select the region you want to apply this to, then select menu item
Data/Validation.
In the Allow dropdown, select List. In the Source textbox, enter
"DHL,FEDEX,UPS" (without the quotes)
* Is there a way to create a formula that will do this type of function
=IF(D25 DOES NOT EQUAL E25 THAN D25 FONT WILL TURN RED) ?
Try Format=Conditional Formatting:
Select D25. Click on FormatConditional Formatting. Set Condition 1 - Cell
Value Is, not equal to, click on E25 on the worksheet. Click on Format,
select the Font tab. Under Colour, choose red. OK, OK.
* Is there a way to enter a formula that will round a value to the nearest
increment of 5?
=ROUND(A1/5,0)*5
* or to the nearest quarter ?
=ROUND(A1/0.25,0)*0.25
* I am looking for a formula that will look at an adjacent column then find
that value in another sheet and return the data from an adjacent column.
Try VLOOKUP.
=VLOOKUP(A2,Sheet2!A2:B100,2,FALSE)
* I want to add the largest/smallest 5 entries in A column.
=SUM(LARGE(A:A,{1,2,3,4,5}))
* I have data stored in rows and I want to change these rows to columns
Select the data, copy it, select where you want it, do editpaste special,
check the transpose option, click OK
* How do I pick 20 random items from a list of 100 ?
Enter the items down A1:A100. In B1:B100 enter formula =RAND(). Sort the
list by B column; top 20 rows is your selection. Press F9 for new B numbers
and repeat for a new selection.
* Is it possible to write a SUMIF worksheet formula to sum visible cells
only?
If the cells are hidden as a result of AutoFiltering, you can use the
SUBTOTAL function, with 9 as the first argument
* I can sum all numbers =10 with =SUMIF(A1:A20,"=10") . But how do I enter
two criterias so I can sum numbers between 5 and 10 ?
That equals sum of all =5 minus sum of all 10:
=SUMIF(A1:A20,"=5")-SUMIF(A1:A20,"10")
Or you can use this method:
=SUMPRODUCT((A1:A20=5)*(A1:A20<=10)*A1:A20)
* Using DataSubtotals, I would like to create a table that has just these
subtotals, not the hidden detail rows.
1. Once you've applied the Subtotal, collapse the outline so that only
the rows you want to copy are visible.
2. Select the cells.
3. Choose EditGo To, click the Special button
4. Select 'Visible Cells Only', click OK
5. Click the Copy button
6. Go to another sheet, and paste
* When I use AutoFilter I don't see all the items in the drop down list. Why
not?
An AutoFilter dropdown list will only show 1000 entries. You could add a new
column, and split the list into 2 or three groups, e.g.:
=IF(LEFT(C2,1)<"N","A-M","N-Z")
Filter on this column first, then by the intended criteria.
Another option is to choose Custom from the drop-down list, and type the
criteria.
* In a cell I have "lastname, firstname". I want to put lastname in one cell
and first name in another.
Use DataText to columns and specify the comma as a delimiter.
* How can I prevent hyperlinks from appearing when I type an email address?
You can turn that option off in Excel XP only. All versions: Select the cell
and press Ctrl+Z, this will convert the hyperlink back to text. The code
below, when run on a selection, will also delete the hyperlinks.
Sub delHyperlinks()
Dim myCell As Range
For Each myCell In Selection
myCell.Hyperlinks.Delete
Next myCell
End Sub
* When I import information from my merchant account the field which holds
the 16 digit credit card information does not display it properly.
Excel only maintains 15 significant digits for numbers and cannot be used
for credit cards which require 16 digits. If you don't need to do math on
them, input long numbers into a range that you've formatted as text.
Both above -and also entries automatically converted to dates- can be
prevented by entering a single ' before the actual entry. Excel will now
treat the cell as pure text and change nothing.
* Why does my function display #NAME?
The function may point to an add-in function that is not avaliable to this
Excel. Most frequently it's an Analysis Toolpack function; go menu Tools
Add-Ins and check that there are checks against Analysis Toolpak.
Unlike Excel's built-in functions, Add-in functions do not translate
themselves to regional language, so american add-in functions are by
default unavaliable on a Norwegian computer and vice versa.
* Is there way of returning the name of a sheet in a cell without using
code?
=CELL("Filename",A1)
returns the complete file path and sheet name
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
will stip away everything but the sheet name. The file must be saved or the
formula will not work.
* My spreadsheet does not calculate correctly ! I'm right, Excel's wrong !
There are three common causes for messages like this.
1. What is displayed in the cells is not what's really there. A cell can
show 1 (no decimals format) but it may well contain real or calculated
values like 0.6 or 1.4. Add or multiply a bunch of those and you're
surprised; Excel will calculate with real cell contents, NOT displayed
contents. You may choose "precision as displayed" in the tools options
menu for a workaround, but make sure you know what you do.
2. A computer use binary numbers, and this has its limitations. It can not
represent numbers like 1/10 exact. Numbers like that are rounded to nearest
15 significant decimal digits, and Excel will be "wrong" around 15th-16th
digit. Some operations suffer from this, and some boolean tests (tests that
may appear as 0.1=0.1) can return False because of this. "Normal work" like
sensible-number budgetting and day-to-day math is usually not affected, but
this may not be the tool for advanced science.
3. You are using Excel's statistical functions. Some of those are not good
enough. LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(),
TREND(), FORECAST(), etc.) uses a numerically unstable algorithm. You're
right, Excel's wrong. Links to backgrounders and tools at
/Excel/Statistics/
* My spreadsheet does not calculate at all !
Calculation is set to Manual, alter this in Tools Options menu.
International issues: These functions are in english, and you can not enter
them i a Swedish Excel as is. Run this macro:
Sub EnterEnglishFunction()
ActiveCell.Formula = InputBox("English function:")
End Sub
paste the function in and OK, and in most cases it translates.
**** Dates and times ****
Very very many Excel questions are about dates and times. Chip Pearson's
webpage /excel/datetime.htm will give you
understanding of how this works in Excel , and it has lots of useful
samples. Here are a very few common questions:
* How do I add times together ?
Just add together just like any number (=A1+A2+A3). Use custom number format
[h]:mm in the result cell to prevent rollover at 24 hours
* How do I subtract time?
Just subtract, like =B1-A1. Use Calendar format 1904 if you need to display
negative results; Tools/Options/Calculation and check the 1904 Date System
checkbox. Note that 1904 calendar will offset all dates by 4 years 1 day, so
be careful.
* I need to calculate a column with hh:mm (formatted for TIME) against a
hour rate. So 0:45 minutes needs to be calculated against 120 per hour -
with an answer of 90. Now it says 3.75 ???
1 is a day; 24 hours. So one hour is 1/24.
=A1*B1*24
will bring the desired result. Format result cell as currency or number (it
tends to pick the date format by default).
* When I copy-paste dates, they end up one day wrong.
* When I copy-paste dates, they end up four years wrong.
One workbook is using 1904 calendar, the other one 1900 (in the first
example, years are not displayed, but definitely present). Go menu
Tools/Options/Calculation and make them equal, preferrably also correct if
you know what the dates was supposed to be.
* When I try to sum the time data in the format: 5:20, 12:02, 20:12 etc. I
get the value that is the real sum minus N*24, eg. 2:07 instead of 50:07.
Use custom number format [h]:mm to prevent rollover at 24 hours
* I'm adding up a large number of cells with seconds in them, i.e... 25, 50
47, etc... the result I would like is 1:10, 1:50: 2:03
Since XL stores times as fractions of days, in order to convert integers
into times you need to divide the sum by (24*60*60) or 86,400
* How do I add 3 months to a date ?
=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
* Could someone give me the series of worksheet functions that would emulate
EOMONTH; last day of month?
The last day of the month equals the zero'th of next month for some strange
reason:
=DATE(YEAR(A1),MONTH(A1)+1,0)
* Excel thinks 1900 is a leap year. It's not.
Yes it does and it's not.
**** Macros, VBA functions ****
* I have a user defined function that doesn't recalculate.
Include all the cells that your UDF depends on in the argument list. Or
enter this as the first statment in your Function:
Application.Volatile
This will cause the function to be executed whenever a calculation occurs in
the workbook.
* All of a sudden, when I open the file, it asks if I want to "Enable or
Disable a Macro". There are no macros in this workbook.
A macro has been added and then removed, leaving an empty module. Open the
file, right click on a sheet tab and choose View Code. Look for modules and
delete them. Empty modules trigger the macro query, as does an actual macro.
While there, make sure all other object's modules are completely empty.
*When a button is drawn onto a sheet the assign macro is not displayed. When
right-clicking on the button the "assign macro" context menu item is no
present.
There are buttons and there are buttons, from the Forms toolbar or from the
Control Toolbox. If "assign macro" is no option then it's the second kind.
Choose "View code" and call your macro from it like this:
Private Sub CommandButton1_Click()
Call Macro1
End Sub
* Some of the functions that we want our macros to accomplish are not
possible without first unprotecting the worksheet/workbook with the
password.
Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"
Be sure to protect your macro code to hide the sheet password.
* I want Excel to run this macro automatically every time the Excel file is
opened.
Place the code in (or call it from) the Workbook_open event of the
ThisWorkbook module in the VB editor. Or simply name your macro Auto_Open.
If you choose to use both then Workbook__open will run before Auto_open.
Auto_open will not run if the workbook is opened by another macro, you must
use the RunAutoMacros method. Contrary; Workbook_open will run if the
workbook is opened by a macro, you must use Application.EnableEvents = False
to prevent it.
* I want to add a toolbar to my spreadsheet that when clicked, brings up
Userform1.
Assing the toolbar button to this macro, which should be in a standard VBA
module:
Sub ShowForm ()
Userform1.Show
End Sub
* I want to show a userform each time my file is opened.
Combine the two solutions above:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
or
Sub Auto_open()
UserForm1.Show
End Sub
See Chip Pearson's /excel/events.htm for detail and
many more useful events.
* Can I ask my user for confirmiation before executing the macro ?
Sub AskAndDo()
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = _
vbNo Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
End Sub
* Can I have my Macro make Excel NOT to ask "the file already exists, do you
want to overwrite" type of questions ?
Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True
* Is it possible to call a macro from the condition true or false side of a
worksheet formula? ie. if(a2="ok",Run macro1,run macro2)
Basically, the answer is No. You can write functions in VBA that you can
call from worksheet cells, but these functions can only return a value. They
can't modify other cells or alter any part of the Excel environment. (See
next Q for a macro solution to the problem)
* How do I run a macro everytime a certain cell changes it's value?
There is an event called Worksheet_Change which is triggered when a value is
entered (it will not fire when a formula result changes). One of the
arguments to this event is 'Target' which is a reference to what changed.
Since this event will occur whenever a value changes - you can use the
target to see if it is the cell you are interested in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C5")) Is Nothing Then
Exit Sub
Else
'The cell you are monitoring has changed!
'Do whatever you need to do...
End If
End Sub
* How do I find the first empty cell in A column ?
If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If
* How do I find the cell below the last entry in A column ?
MsgBox Cells(65000, 1).End(xlUp).Row + 1
(This will return 2 on an empty A column)
* How do I find the last row in my spreadsheet ?
MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
* I want to loop through all selected cells and perform an operation on each
of them.
Sub DoOnSelection()
Dim oCell As Range
For Each oCell In Selection
oCell.Font.Bold = True
Next
End Sub
* I want to loop through all worksheets and perform an operation on each of
them (unprotecting or whatever).
Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
MsgBox ws.Name
Next
End Sub
* I want to loop through all workbooks in a folder and perform an operation
on each of them.
Sub AllFolderFiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
MyPath = "C:\Temp"
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile < ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
MsgBox wb.FullName
wb.Close
TheFile = Dir
Loop
End Sub
* How can I tell if a file exists in a spesific folder ?
Function bFileExists(rsFullPath As String) As Boolean
bFileExists = Len(Dir$(rsFullPath))
End Function
* How can I tell if a spesific workbook is open ?
Function bWorkbookIsOpen(rsWbkName As String) As Boolean
On Error Resume Next
bWorkbookIsOpen = Len(Workbooks(rsWbkName).Name)
End Function
* I want to let the user select a file within my macro.
Sub SelectWebPageToOpen()
Dim ThePage As Variant
ThePage = _
Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _
, "Pick one:")
If ThePage = False Then
MsgBox "You cancelled"
Else
MsgBox "Do something with file " & CStr(ThePage)
End If
End Sub
* I want to let the user enter a "Save As" location in my macro.
Sub SelectSaveFileName()
Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename("C:\Temp\File.xls", _
"Workbook (*.xls), *.xls", , "Your choice:")
If TheFile = False Then
MsgBox "You cancelled"
Else
MsgBox "Do something with file " & CStr(TheFile)
End If
End Sub
* Is there a way to hide the process of executing macro?
Application.ScreenUpdating = False
'code here
Application.ScreenUpdating = True
*Is there a way to protect the macros I create so people can't see or alter
them?
Go to Tools VBAProject properties, lock the project for viewing, and enter
a password.
* How can I unprotect a VBA project using code ?
You can not. A workaround is to simulate keystrokes with the SendKeys
method.
* How do I close a file/close Excel with a macro ?
ActiveWorkbook.Close savechanges:=False 'true ???
will close the active workbook
Workbooks("mywkbk.xls").Close savechanges:=False 'true ???
will close mywkbk.xls
ThisWorkbook.Close savechanges:=False 'true ???
will close the workbook that holds the code that's running.
Application.Quit
will close all of Excel. Be careful with this one.
**** I didn't find my answer here, now what ? ****
First, do a search on /advanced_group_search to see
wether a similar question has been answered before. Use *excel* in the
newsgroup field. See 's
/dmcritchie/excel/xlnews.htm for more on archive search.
If no luck, post a question. Please follow these guidelines for a best
possible result:
* Tell us what versions of Excel and Windows you're using
* Use an illustrative subject line, not "Excel problem" or "Help"
* Don't post to more than one group. People don't like wasting time helping
you if your problem is already solved elsewhere.
* If you've already tried using some formulas or VBA, include what you've
already tried. You may be very close.
* Please don't ask us to email and don't follow up answers by direct email
unless you are invited to.
* Finally; do not attatch files. Attachments are frowned upon for a variety
of reasons:
- newsgroup Bloat.
- download time. Many (most?) people pay their phone company a per-minute
rate for connections.
- virus concerns. Many won't or aren't allowed to open such files.
- they takes up space on individual hard drives
- they are not stored on newsgroup archives.
- Take the time to explain your problem. Who knows, by writing out the
question, you may even figure it out yourself.
This list is condensed from Chip Pearson's webpage
/excel/newposte.htm.
**** Good resources on the web ****
There is a very good Excel functions workbook by Peter Noneley at
/noneley/ . Recommended.
Comp.Apps.Sprreadsheets FAQ is located at
/faqs/spreadsheets/faq/ .That one's stuffed with good
links, some may be too old though.
There are many good Excel webpages, and MVPs' / Frequent posters' signature
addresses are all worth a visit.
Instead of creating yet another links collection, let's just say Start Here:
-walk.com/ss/excel/links/index.htm
Finally:
* What is an MVP and which exams do I take to become one ?
MVP is an award that Microsoft give those who help people with using MS
products and do it well. So stay here and provide lots of brilliant answers,
then see what happens. There are no other exams than "practice, practice,
practice". The MVP program is presented at /
|
| 4050 |
I am a teacher who mail merges percentages from my Excel
gradesheet to word. When I went to the new version of
Excel XP Home Edition, the percentages began to be
converted to what looks like a text format. What was a
regular percentage, such as 95.82 in Excel,became a
95.82345689743567 in my Word document. I have tried to
get help but noone seems to be able to deal with this
problem. Is there anyone out there who can help me?
jim
|
| 4088 |
Really don't know anything about XP, but just from your description of the
problem, I would suggest that you change your formulas in XL.
It sounds as if Word is importing the "actual" value of the XL cell. I
would suggest, therefore, that you change the "actual" value in the XL cell
by using the Round() function.
For example, if for instance, your original formula to calculate a grade
might have been averaging the results of 4 exams using in A5:
=AVERAGE(A1:A4)
Where A1 to A4 contained formulas to give you the individual exam results.
In this case, you would never see the actual value of the cell because the
formula itself would be displayed in the formula bar, and the *formatted*
results would be displayed in the cell. The cell value could well be what
you said word imported!
You could easily change your formula to:
=ROUND(AVERAGE(A1:A4),2)
This would force XL to make the "actual" value of the cell a 2 decimal
number.
HTH
RD
"jim" <denekaj@lemarscomm.net wrote in message
news:0fa501c23cba$97c35620$39ef2ecf@TKMSFTNGXA08...
I am a teacher who mail merges percentages from my Excel
gradesheet to word. When I went to the new version of
Excel XP Home Edition, the percentages began to be
converted to what looks like a text format. What was a
regular percentage, such as 95.82 in Excel,became a
95.82345689743567 in my Word document. I have tried to
get help but noone seems to be able to deal with this
problem. Is there anyone out there who can help me?
jim
|
| 4089 |
I've never merged, but I've saved a couple responses from Deb Dalgleish.
She's recommended this to other people with formatting problems between excel
and word.
==============
In Word, after you have inserted the Merge fields, press Alt+F9, to view
the field codes.
In the field code for the Zip Code, you can add a switch to format the
number. For example:
{ MERGEFIELD "Zip" \# "00000" }
Press Alt+F9 again to hide the field codes, then view the merged data.
So I spent hours trying to get it to format as percentage.
This seemed to work for me (but it's the first=last time that I'm playing with
MSWord!)
{ = { MERGEFIELD "pct"} *100 \# "00%" }
Word makes excel look too easy!
jim wrote:
I am a teacher who mail merges percentages from my Excel
gradesheet to word. When I went to the new version of
Excel XP Home Edition, the percentages began to be
converted to what looks like a text format. What was a
regular percentage, such as 95.82 in Excel,became a
95.82345689743567 in my Word document. I have tried to
get help but noone seems to be able to deal with this
problem. Is there anyone out there who can help me?
jim
--
Dave Peterson
ec35720@msn.com
|
| 4327 |
Take the table at /master/street.htm and paste it
into Excel XP. The fractions in the left-hand column will be converted to
dates. For example 1/4 is converted to 37347 which is displayed as 01-Apr.
In this instance I'm using the English as opposed to the US date system but
that's not relevant.
I have used all sorts of Paste Special combinations to no avail. I get the
same thing trying to paste the data into Access.
It seems so simple, but has anyone got any idea how to get fractions pasted
into a spreadsheet as (in the example) "1/4" ?
|
| 4329 |
Take the table at /master/street.htm and paste it
into Excel XP. The fractions in the left-hand column will be converted to
dates. For example 1/4 is converted to 37347 which is displayed as 01-Apr.
In this instance I'm using the English as opposed to the US date system but
that's not relevant.
I have used all sorts of Paste Special combinations to no avail. I get the
same thi |