|
Conditional Format formula using VBA
I need help setting up a Conditional Format formula using VBA.
Currently I fill a lot of rows of data with a simple formula using
this code:
There are several versions of this code in the module, each one
covering a unique data type ‘QD indicates the data type.
Sub condFormatingDEV()
'QD
Dim qdNm As Name
Dim qdNmRng As Range
Dim qdNmRngA As Range
Dim qdNmRngB As Range...
|
2 |
12/11/2008 10:01:31 PM |
|
|
macros - any other way around xlstart folder?
I've created a macro to format a data file that is created by a data logging
machine. I want this macro to run (via keyboard shortcut) whenever I open
this type of file. Since this macro isn't used that often, it's annoying when
personal.xlsb opens every time excel starts. Is there a way around this where
personal.xlsb doesn't open, but the macro will still be available no matter
what xl...
|
3 |
12/11/2008 9:31:01 PM |
|
|
Disable Data Connection
Hi,
I am using Activeworkbook.Refreshall in one of my functions.
But when I go offline its displaying dialog box "Microsoft Access Database
Engine" to connect again.
But I want to disable the dialog box or close the connection if I go offline.
Can anybody let me know how i can do this?
Thanks.
...
|
4 |
12/11/2008 9:29:02 PM |
|
|
Save Workbook As .xlsx to Remove Macros?
Hi everyone, reading posts on this board have been extremely helpful in
writing my macros. I've got a file running some macros to get up-to-date
information upon opening. The last step is to allow users to save a copy or
snapshot of this information their local drive, but to do that I need to
strip all the macros from the code so they are just left with the data that
was generated when...
|
3 |
12/11/2008 9:23:01 PM |
|
|
I need a formula
that will look through 2 columns for a date and add the figures in another
column into a cell for that date.
example:
Dec name amount payment1 payment2
1st 100.00
5th client 100.00 dec1 jan 1
10th
20th
25th
Jan
1st 100.00
5th...
|
2 |
12/11/2008 9:23:01 PM |
|
|
Date and Time Picker
Hi all, I have Date and Time Picker control on my Sheet and i also
have a Command Button on my Sheet. When i click on Date and Time
Picker control drop down arrow then a small calander appears from
which i can select the date. How can i do the same thing by clicking
the Command Button. In other words i want a macro in Command Button
that when i click the button it should show the calander ...
|
1 |
12/11/2008 9:01:22 PM |
|
|
Formula Problem in macro
Hi Folks,
This seems strange to me... I have a formula which works fine...
=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)
but when I try to insert that via a macro I get an error.
Range("A5").Select
ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive
Phone List.xls'!A5)"
Produces:
#NAME?
TIA,
Steve
...
|
10 |
12/11/2008 8:32:31 PM |
|
|
Application.Onkey for ^F not working
Hi,
I have given a macro for Application.Onkey ^F in Workbook Activate event and
reset it in the workbook Deactivate event. Its working fine in my workbook
but in other excel workbooks when I did ^F nothing happening.
I couldnt findout the reason why its not working in other excels.
Can anyone help me out here.
Thanks....
|
3 |
12/11/2008 8:30:01 PM |
|
|
Change syntax
Hi,
I'd like a macro that does the following logic when I press a command button
- how do I format this "code" for Excel's Visual Basic?
onclick {
if cell W6 of this worksheet is not empty or zero, copy it to field W6 on
worksheet "Sales" - unless that field is already full, then prompt to replace
or leave existing value.
}
Also, how do I clear a particular field in a different wo...
|
6 |
12/11/2008 8:27:06 PM |
|
|
Date Selector
I have data validation on a cell in a worksheet. It has a list with
yesterday, today, this month, last month, this quarter, last quarter, this
year, last year. How would i program that when one of these items is
selected the start date and end date are entered into two cells onto the
worksheet?...
|
3 |
12/11/2008 8:22:17 PM |
|
|
Print Macro
I currently have the below Macro that will change the paper size and
position. How do I add a line that will make the contents in the spreadsheet
to fit on one page. However, I have about 152 pages. Help - thanks! I get
errors when adding ActiveSheet.PageSetup.PrintArea = "". Do I need to place
it in a certain order. See below for entire Macro.
mySht.PageSetup.PaperSize = xlPaperLegal
...
|
2 |
12/11/2008 8:18:48 PM |
|
|
Excel crashes
Hi newsgroup
I have a little problem with my excel app (Excel 2000, 2003)
For data entering reasons I am using a Form. On this Form I use Textboxes
(also Comboboxes) which are direcly linked to a cell (controlsource) and
other ones which I fill with data throught code on the events Activate and
QueryClose. This textboxes are formated by event exit (later more).
Here are some code snipp...
|
2 |
12/11/2008 8:13:26 PM |
|
|
Error In VBA
When certain computers open my user form it will give me the debug. But only
on two of 18 computers do it. Am I missing something?...
|
5 |
12/11/2008 8:10:05 PM |
|
|
ControlTip Text
I have added several command buttons to an spreadsheet in Excel 2003. Is it
possible to add a control tip text whenever the mouse is moved over these
command buttons? The command buttons are on the sheet and not in a form.
Thanks for the help.....
--
JT...
|
3 |
12/11/2008 8:06:48 PM |
|
|
DateSerial function
I would like to calculate rollong calendar from today in the loop.
For example, today is Dec/11/2008 and I need to get all informaiton 12
months before today. (from Dec/11/2007 to Dec/11/2008).
I just wonder can DateSerila do the job.
For example, DateSerial(MyYear, MyMonth - i, MyDay)
Will DateSerial changed the year if the month is minus number?
Your information is great apprecia...
|
4 |
12/11/2008 7:59:41 PM |
|
|
Create Multipe folders and naming them by Cells in Columns using vba
Hello,
I have a worsheet where the Last Names will make up a large folder
directory where we will store scanned documents by last name.
I have an excel workbook named "Student Name"
How do I take the 1100 or so names in column A and create empty
folders named by the cells in Column A in a folder named "Test" on the
desktop?
Thank you...
|
4 |
12/11/2008 7:44:53 PM |
|
|
Forms-Compile error: user-def type not defined
I copied the following code from www.contextures.com/xlDAtaVal14.html--but i
rcv the error above.
I just copied the subroutine so that the cursor moves to the next cell if
tab or enter key are pressed. i am not familiar with VBA code, so i hv no
idea what i need to revise:
====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code...
|
2 |
12/11/2008 7:40:01 PM |
|
|
Conditional formating not working
Greetings all. I have a formula in E3, =SUM(D6:F6). I have a formula in E4
which is to sum three other cells. In E5 I have formula =E4-E5. I have
conditional formating on E5 as cell value is equal to 0, change background
color to green. If the value of E5 is 0, based on the formula in it, the
conditional formatting does not work. However, if I manually type in 0, then
it turns gree...
|
6 |
12/11/2008 6:49:01 PM |
|
|
need to delete files
Hi,
I need to delete some files in a specific folder.
Some files are resaved with brackects and I need to keep the file with the
highest number in the brackect and delete the one that existed before.
For example If I have the following files in folder "Myfolder"
Myfile1.xls
Myfile2.xls
Myfile3.xls
Myfile2(2).xls
Myfile3(2).xls
Myfile3(3).xls
I should end up with
Myfile1.xls
My...
|
2 |
12/11/2008 6:46:36 PM |
|
|
Importing stored procedures into Excel with parameters
I wish to import the results of a sql stored procedure into Excel. The
stored procedure has two date parameters yyyy-mm-dd and an interger
subscription id parameter. The user needs to be able to change the dates and
subscription id.
I haven't been able to set the import up without putting in fixed dates and
a subscription id as Microsoft Query doesn't allow parameters to be set up i...
|
5 |
12/11/2008 6:45:01 PM |
|
|
Compare and Highlight
I need help figuring out how to do the following.
I have 2 work books "Book1" and "Book2"
Columns A & B of Sheet1 in both workbooks contain First Names and Last Names.
I want to compare the workbooks and if the name in Book1 appears in Book2 I
want it highlighted in Book1.
Is this possible?...
|
2 |
12/11/2008 6:38:33 PM |
|
|
Help writing macro
I have a user that needs to be able to hover over a cell and see the entire
contents of the cell. We thought it might be beneficial to create a macro
that would copy the entire contents of the cell and create a comment (since
the cells only show 250 characters and comments can hold more). We would
need this for each and every cell. Any ideas? I need this ASAP. Thanks....
|
4 |
12/11/2008 6:32:00 PM |
|
|
Re: Unprotecting Several Sheets At Once
I have some code entered in to protect all the sheets upon close however I
would like to be able to unprotect them all by only entering the password
once as it is the same for all the sheets.
Thanks
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="password"...
|
4 |
12/11/2008 6:29:00 PM |
|
|
using string functions within a range?
I think I know the answer to this question, but I am looking for a
suggestion to try...
Background
I have an inefficient piece of code that is determining whether to
process a record before copying it into the macro file. I was doing
this line by line originally but it is turning out that it has to
process approx. 30,000 rows (takes 10 minutes to run). So I am in the
process of converti...
|
2 |
12/11/2008 6:27:03 PM |
|
|
Parent property of custom class
I have a custom class and one of its properties is another custom
class
Specifically I have a class which interfaces to an external
application to grab data, and a generic sub-class which works out
statistics.
pseudo-code...
set V = new clsCalItem
V.Stats.reset
while not finshed
V.stats.add
wend
msgbox V.stats.count, v.stats.mean
and clsCalItem contains the line
dim Stats as...
|
4 |
12/11/2008 6:24:41 PM |