Q. Is there aneasy way to disable the Exit menuitem from the system menu ? I would also like to disable Alt+F4 and the X icon in the top right corner (Win95).
A. From Hans Wilhelmsson
You can easily remove menu items by using the
menu editor in the tools menu (available if you have an module
active).
Conserning Alt +F4 you may try the onkey event (Look it up in
help)
Q. My problem is this: I'm trying to get data
from an Access table using a wild card place holder and I'm not
getting data returned to Excel. Here is my SQL statement being
passed to Access via ODBC
QueryString = "SELECT Component, PctWgt, BlendID FROM
Ingredients " & _
"WHERE Component = ('Exxon*') "
A. From Irek Grgorczuk
Single quotes and % will do it
Another suggestion was :
QueryString = "SELECT Component, PctWgt,
BlendID FROM Ingredients " & _
"WHERE Component LIKE ('Exxon*') "
Q. I need to determine where my page breaks are occurring. Specifically I need to know in which row(s) page breaks are occuring (print area is only one page wide).
A. From Rob Bovey
The macro below will give a page break summary
Sub FindPageBreaks()
Dim szResult As String
Dim rngRow As Range
Dim rngTarget As Range
With Worksheets("Sheet1") _
.Range("Print_Area").EntireRow
Set rngTarget = .Resize(.Rows.Count - 1) _
.Offset(1, 0)
End With
Q. How to place translucent watermarks in an
Excel document (similar to the way that WordPerfect does)?
A. From Rob Bovey
There's a SetBackgroundPicture method of the
worksheet and chart objects that you can use to place a bitmap
into the backround of these sheets. It tends to balloon your file
size though. Here's a quick sample of how to use it.
Sub SetWatermark()
ActiveSheet.SetBackgroundPicture
"d:\graphics\watermrk.bmp"
End Sub
Q. How can I get Excel to print non-contiguous
columns that have been selected (using Ctrl+Click) on the same
page. I have used Column, Hide to hide the columns in between,
but to no avail; I still get one page per column. I have checked
"Print Selection Only" in the Print Setup options,
also.
A. From Simon_Daykin
It should work right with the columns hidden
(you may have to reselect the print area).
Q. How do you use the YEAR function to grab
year from a date and put it into another column ... shouldn't
this work?
A. From Dave Boylan
Format the year column as general. OR, format
the date column as "YY"
Q. I am having a problem with zipcode
formatting and Northeast zipcodes that begin with 0 -- i.e.
04901. If I enter 04901, 04901 is visible in the cell & 4901
is in the formula bar. My big problem arises when this data is
exported as a csv file -- the 04901 zipcode is 4901 in the csv
file.
I have tried formatting it as text -- the 0 still gets dropped.
Only typing it in as '04901 works - as long as the typist
remembers the '.
Sometimes I get a type mismatch error for some cells -- but it
ends up exactly like we want it and exports exactly as it looks
-- for both 5 & 9 digit zips. If I don't get the error then
the 0 is dropped. If I could only get rid of the error message
and keep the macro running.... maybe it would suffice. Except
that I have no idea why it does this in some =
cells but not others.... and not always with the same cell twice.
A. From Judy Lathrop
Here is a technique that worked for me. Perhaps
it will be useful
to you.
Function RtJustNo(pOutNum As Variant)
Const csField5 = 5
Const csLead5 = " " '5 spaces
Dim iLen As Integer
Dim sNum As String
sNum = Format(pOutNum, "00000;-00000;00000")
iLen = Len(sNum)
sNum = Left(csLead5, csField5 - iLen) & sNum
RtJustNo = sNum
End Function