MSGROUPS.NET | Post Question | Search | About | Groups | Contact | Register | Login



microsoft public excel misc (5666)

microsoft public outlook general (4816)

microsoft public excel programming (4187)

microsoft public word docmanagement (3506)

microsoft public excel worksheet functions (3180)

microsoft public access (3139)

microsoft public windowsxp general (2033)

microsoft public windows vista mail (1618)

microsoft public office misc (1571)

microsoft public windows vista general (1507)

microsoft public windows live mail desktop (1354)

microsoft public access queries (1319)

microsoft public powerpoint (1268)

microsoft public access formscoding (1206)

microsoft public mac office entourage (1193)

microsoft public dotnet languages csharp (1174)

microsoft public access forms (1153)

microsoft public internetexplorer general (1123)

microsoft public access modulesdaovba (1065)

microsoft public windows server sbs (1060)

microsoft public greatplains (1045)

microsoft public mac office word (1018)

microsoft public outlook calendaring (961)

microsoft public excel (928)

microsoft public sqlserver programming (897)

microsoft public project server (874)

microsoft public windowsupdate (812)

microsoft public windowsxp help and support (798)

microsoft public windowsmedia player (781)

microsoft public project (772)

microsoft public mac office excel (759)

microsoft public access reports (758)

microsoft public exchange admin (747)

microsoft public dotnet framework aspnet (736)

microsoft public sqlserver server (700)

microsoft public outlook (697)

microsoft public outlook contacts (678)

microsoft public dataprotectionmanager (659)

microsoft public word vba general (635)

microsoft public windows server general (628)

microsoft public windows server active directory (586)

microsoft public outlook installation (580)

microsoft public word newusers (560)

microsoft public publisher (517)

microsoft public excel newusers (507)

microsoft public excel charting (499)

microsoft public word pagelayout (494)

microsoft public dotnet languages vb (464)

microsoft public vb general discussion (457)

microsoft public development device drivers (444)

microsoft public office setup (430)

microsoft public word application errors (428)

microsoft public sharepoint general (426)

microsoft public windowsce platbuilder (409)

microsoft public access gettingstarted (407)

microsoft public windows powershell (400)

microsoft public frontpage client (391)

microsoft public onenote (391)

microsoft public sqlserver reportingsvcs (382)

microsoft public outlookexpress general (369)

microsoft public access tablesdbdesign (352)

microsoft public pos (352)

microsoft public vc mfc (346)

microsoft public outlook program vba (341)

microsoft public windows live messenger (324)

microsoft public money (309)

microsoft public windowsxp hardware (299)

microsoft public windows 64bit general (295)

microsoft public scripting vbscript (289)

microsoft public windowsxp basics (236)

microsoft public windows mediacenter (230)

microsoft public dotnet general (225)

microsoft public mac office (198)

microsoft public nntp test (195)

microsoft public windows inetexplorer ie6 outlookexpress (191)

microsoft public windows vista music pictures video (153)

microsoft public win98 gen discussion (153)

microsoft public sharepoint setup and administration (139)

microsoft public windows vista hardware devices (130)

microsoft public dotnet framework (104)

microsoft public windows vista performance maintenance (97)

microsoft public visio (89)

microsoft public access 3rdpartyusrgrp (73)

microsoft public dotnet xml (59)

microsoft public access conversion (56)

microsoft public pt windowsxp (36)

microsoft public windows live photogallery (32)

microsoft public access developers toolkitode (31)

microsoft public dotnet faqs (15)

microsoft public dotnet framework aspnet datagridcontrol (10)


microsoft.public.excel.programming Post New
Items(4186) /42 Next >> Last >|
Subject Posted Replies From
change date format to excel format

Hi, I have a date in 01.03.2010 format, how can I change it by using vba for every time I import the data?

3/11/2010 9:08:01 AM 0 =?Utf-8?B?TW90dGE=?= <Mo...@discussions.microsoft.com>



Select specific worksheets & copy - code problem

Hi All I've tried to write the following code to: - find worksheets in active workbook with "Planned" in cell A1, - then select all those worksheets and copy them into one new workbook - then format each sheet within the new workbook (e.g. select a named range & copy/paste values etc) The workbook and worksheet names are dynamic I'm sure I've got the "End If"'s / "Next" in the wrong place (this always confuses me) At the moment it copies the active worksheet (which does not have "Planned" in cell A1) and a blank new worksheet? - and then it stops. Any help would be greatly ap

3/11/2010 9:05:01 AM 0 =?Utf-8?B?QmVTbWFydA==?= <BeSm...@discussions.microsoft.com>
Copy cell range to another sheet

Hi Guys, I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. Sub copy_1() Dim SourceRange As Range Dim DestRange1 As Range Dim DestRange2 As Range Dim DestRange3 As Range Dim DestRange4 As Range Dim DestSheet1 As Worksheet, Lr As Long Dim DestSheet2 As Worksheet Dim DestSheet3 As Worksheet Dim DestSheet4 As Workshee

3/11/2010 8:21:01 AM 2 =?Utf-8?B?QWxiZXJ0?= <Alb...@discussions.microsoft.com>
Error Handle problem

Hi, I tried to copy some filtered rows. If there is no rows to copy, it will encounter: Run-time error '1004', No Cells were found. ie the code stops at "result.SpecialCells(xlCellTypeVisible).Copy" Then I change the code to "If result Is Nothing Then " and continue, It smoothly completes the process. However, it fails to copy when there are rows to copy ie the code skips copying the rows and reach at End If Codes Extract Sheets(1).Select Dim Lrow As Long Dim result As Range Lrow = Range("A" & Rows.Count).End(xlUp).Row Set result = Range("A2:G" & Lrow + 1

3/11/2010 8:06:12 AM 0 Len <ltong2000...@yahoo.co.uk>
Adding date and time to cell programatically?

Hi all, i have been trying to check a cell that contains NOW() for a time and populate another cell based on an evaluation of a formula with a concatenation that looks like NOW(). I need to put a certain date AND a certain time in a cell but it can't be text as the cell is used by the network for PI tag data retrival, as you can see from the coe i have tried a number of things, the cell is custom formatted to dd/mm/yyyy hh:mm. Any ideas? VBA Code: -------------------- Sub Formula_Add() Dim eformula, d As Date, MyTime, MyTime1 Application.ScreenUpdat

3/11/2010 7:49:44 AM 1 Simon Lloyd <Simon.Lloyd.47n...@thecodecage.com>
touch screen apps

just wondering if anyone has developed apps that work with a touch screen. any info would be appreciated. thanks -- Gary Keramidas Excel 2003

3/11/2010 7:20:16 AM 0 "Gary Keramidas" <gkerami...@MSN.com>
linking cells in a column (result) to cells in a row (source)

MS XP Pro / Excel 2007: Good day all. I have data in two consecutive rows. The 1st one is text and the second contains formulae which results in values. (Call these the "source") On another sheet in the same workbook I have two columns (Call these the destination) which need to be linked to the source rows if value (other than zero) is present in the 2nd source row. There could be zero value or blank cells within the source rows. I need code to sequentially seek values (other than zero) in the cells in the 2nd row of the source and when found, the next available cell in the d

3/11/2010 6:17:01 AM 0 =?Utf-8?B?V2VzX0E=?= <W...@discussions.microsoft.com>
Pasting to Cells to the Right.

A sheet contains AC NO, DATE, and Amount of Clients. 116530 17-Feb-10 2000.00 39361 17-Feb-10 300.00 40236 18-Feb-10 350.00 10563 18-Feb-10 100.00 116530 19-Feb-10 250.00 Ac Nos are names of the excel workbook (116530.xls) located at D:\conference\ AC No Workbook looks like this. A Date, B Time, C Country, D Rate, E Minutes, F Total, G is blank for remarks, H is date and I is Amount. 3-Mar-10 7:37 PM UK 1.00 58 58.00 2-Mar-10 200.00 3-Mar-10 8:46 PM UK 1.00 57 57.00 3-Mar-10 9:30 PM UK 1.00 25 25.00 4-Mar-10 500.00 1. I have to find first empty cell

3/11/2010 5:53:01 AM 0 =?Utf-8?B?WEtydW9kbw==?= <XKru...@discussions.microsoft.com>
Suspend Scrolling in a split pane

I have a worksheet where I have split the window into 2 panes. In the top pane, I want to display rows 17-22, and I don't want people to be able to scroll from those rows in the pane. In the bottom pane, I want to be able to scroll up and down as usual (row 1 - n). Thanks in advance

3/11/2010 2:37:01 AM 0 =?Utf-8?B?UGFt?= <...@discussions.microsoft.com>
Odd behavior using CONCATENATE

Hello, I have one spreadsheet where I am trying to use CONCATENATE to combine some text and a number. The cell T6 contains "AT COST X " The cell S7 contains a number 1.18 The cell S8 contains =CONCATENATE(T6,S7) I get a #VALUE error on this, and I can't figure out why. Any ideas?

3/11/2010 1:18:52 AM 2 Andrew <andrewkgent...@gmail.com>
Control to pick time?

Are there any options or controls which allow someone to easily select a time? The DTPicker control seems to be heavily designed for choosing dates, and I'd like something that might display a clock or something. Thank you

3/11/2010 12:59:27 AM 3 "Robert Crandal" <nob...@gmail.com>
Printing of option buttons

I have a file that contains 3 option buttons on one of the sheets. Printing of this sheet is prevented except via a command button on the sheet, which activates a custom print Sub that I wrote. (I used the BeforePrint event and a Boolean variable to restrict this). For some reason, the hard copy prints of this sheet do not show which of the 3 option buttons is currently selected. The option buttons are printed, but the "hole" for each one is empty in the print even though one is clearly selected on the screen. I have another file with an apparently identical situation in whi

3/10/2010 11:56:07 PM 1 =?Utf-8?B?TmVwdHVuZSBEaW5vc2F1cg==?= <wor...@halfchopper.com>
Check if value does not exist in range.

Hello I have a small question for the gurus here: I have a spreadsheet with Data. The data is stored in columns A through P with column labels in row 1. So my column lables are A1:P1 I know how to use: Cells.Find(What:="SomeColumnLable").Column To determine which column any particular bit of information is located at. My question is: Is there a way that I can use the Cells.Find to check if a column label does not exist in the range? Thanks JC

3/10/2010 9:48:03 PM 3 =?Utf-8?B?SnVhbiBDb3JyZWE=?= <JuanCor...@discussions.microsoft.com>
Macros Help Needed, Thanks in Advance

I have an Excel workbook with approximately 50 worksheets; I have been able to enter into the VBA the following a) automatically name worksheets, b) sort worksheets by name,and c) utilizing uppercase characters in specified areas. My problem is with the first worksheet, which is a master inventory sheet, I created a macro and appropriately used the shortcut "Ctrl A"; the Macro works perfectly when the sheet is unprotected; the problems begin when I lock all the sheets, for obvious reasons, then I get a message error. Is there a way to by-pass this; I cannot leave the workbook u

3/10/2010 9:42:01 PM 4 =?Utf-8?B?Y2hyaXMgZmVsaXg=?= <chrisfe...@discussions.microsoft.com>
Searching question

My sheet contains several rows of data and the number or rows will grow each day. At the end of the year, I'm guessing that this sheet will contain between 500 and 600 rows of data. I created a search macro which basically steps though each row one at a time (using a For-Next loop). I then do simple string comparison to check if the row of data matches the search criteria. If anyone else here was given the task of creating a similar searching system in Excel, what would you do?? I often hear about using auto filters and VLOOKUP & MATCH functions, so I often wonder if my searc

3/10/2010 8:33:05 PM 3 "Robert Crandal" <nob...@gmail.com>
Repost - checking Forefront updates

Using VBA does anyone know how to retrieve the latest Forefront updates date? -- Steve

3/10/2010 8:28:11 PM 0 "AltaEgo" <Somewh...@NotHere>
name manager?

Morning all. I'm trying to make a combo box and have come to realize that I'd probably be better off using the name mgr to set my values. I have a worksheet that's 4830 rows of data that I'd be using as my source. Due to the type of data, its arranged in a manner that has a lot of spaces between cells of data. E.g. I have my column A data that varies between single rows, and having gaps upwards of 20 rows. That data on column A is correlated to the data in columns B-K, and the B-K data is what makes for the gaps. So far, I tried setting the ListFillRange of the Combobox, fo

3/10/2010 7:22:05 PM 3 =?Utf-8?B?U3RldmU=?= <St...@discussions.microsoft.com>
Copying Data from a workbook to another workbook

I am trying to copy data in a merged cell from workbook/worksheet to another workbook/worksheet where the receiving cell may be a different location and different size. This is to be done via a VB macro because I will be doing multiple worksheets.

3/10/2010 7:17:02 PM 1 =?Utf-8?B?UGFt?= <...@discussions.microsoft.com>
PivotTable: formula for added & removed

Everymonth I get a list of office codes, and I need to compare if there are any changes (additions or deletions). Here is how the raw data comes in: Group Office Main Office Codes Date XBA MOFC 104 104 Aug-09 XBA MOFC 104 133 Mar-10 Then I create a pivottable where Group & Office are a page then Main & Office Codes are rows with Date and a column and the Count of Office Codes is the data. This way the months are side by side and I can visually see if there is a new code or old code removed. I want to go one step future and instead of doing it visually

3/10/2010 7:13:02 PM 0 =?Utf-8?B?S3J5c3RhbCBQZXRlcnM=?= <KrystalPet...@discussions.microsoft.com>
Pivot

-- Krystal K. Peters

3/10/2010 7:07:02 PM 0 =?Utf-8?B?S3J5c3RhbCBQZXRlcnM=?= <KrystalPet...@discussions.microsoft.com>
Lost locals and watch windows

Somehow I have contrived to kill the Locals and Watch Windows in VBA. when I try to enable them in the view menu nothing happens. Last night I had undocked them and dragged them to an unused portion of the screen but when I shut down, I think they were still visible. Today they are gone. HELP!

3/10/2010 6:51:14 PM 1 Robert H <robert.hatc...@l-3com.com>
BeforeDoubleClick Event not working

I have this BeforeDoubleClick event, below, that I am trying to run. Everything was working fine yesterday now, it just jumps from the "If" to the "End If" statements. I can't figure out what's going on. Any ideas? Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim errorWS As Worksheet, siteWS As Worksheet Dim i As Integer, rw As Integer Set siteWS = Worksheets("Site Milestone Dates") Set errorWS = Worksheets("Error_MarketList") errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row If Target.Address() >= "$A$5" Or Target.Address

3/10/2010 5:36:01 PM 2 =?Utf-8?B?QXlv?= <...@discussions.microsoft.com>
How do I make a Binary Search Algorithm on Excel?

Basically I have to search a data set via customer codes using a binary search algorithm and have no idea how to do it? I'm very new to VB so would appreciate some simple words :)

3/10/2010 5:26:01 PM 3 =?Utf-8?B?TmV3VG9WQg==?= <NewT...@discussions.microsoft.com>
Index match formula required

So I think what I need is a index match formula but not 100% sure. Basically what I need is an insurance calculator based on age, sex and smoking status this is what your premium would be. One work sheet one are my variables. Colum B I have used validations so only the specific cl can not change them. A B Enter current age 30 - 34 Male or Female Male Smoker or Non Smoker Non-smoker Desired amount of coverage 50,000 Units of coverage required 50 Worksheet 2 r

3/10/2010 5:21:21 PM 1 "Kristina1976 via OfficeKB.com" <u51...@uwe>
Personal.xlsb problems

If I open xl2007 via the excel.exe (or shortcut) it will load the macros contained in the personal.xlsb file. However, if I click on an existing .xlsx file, the personal.xlsb file is not loaded (not even hidden). I checked my add-ins to ensure that the personal.xlsb file is disabled (thinking excel might have thought it was corrupt), but it doesn't show disabled. So, why does xl2007 load the macros if I open a new workbook, but not when I open an existing workbook? Thanks!

3/10/2010 5:09:02 PM 0 =?Utf-8?B?VkJBX05ld2I=?= <VBAN...@discussions.microsoft.com>
Create OptionButton at run time

I their a way I can create an option button at run time using vba and place it on a specific worksheet named "Admin" Thanks.

3/10/2010 4:56:01 PM 3 =?Utf-8?B?SmFtZXM=?= <Ja...@discussions.microsoft.com>
Delete ' from date

Hi, i have 10523 lines with dates in col D, with dates written as '27/10:09 etc. What code could i use to find all the ' and delete them? Thanks

3/10/2010 4:37:01 PM 3 =?Utf-8?B?TGlBRA==?= <L...@discussions.microsoft.com>
not like syntax in querry not working

Hello all- I am pulling data from an access dbase and have ran into a syntax issue. Does anyone what is wrong with the last line? The data pulls, but ignore the not like statements and i get all the items with gfm and cfm in the material name along with the data im looking for. I need to exclude all records with GFM or CFM in the material field. Please help strsql = "SELECT PEGG_TASSRAW.Plnt, PEGG_TASSRAW.Material, CJI3ByReplen.Description, CJI3ByReplen.PurchDoc, PEGG_TASSRAW.[Replenishment Element], PEGG_TASSRAW.[Grouping WBS el], PEGG_TASSRAW.[Assigned WBS], PEGG_TASSRAW

3/10/2010 4:25:01 PM 0 =?Utf-8?B?ZG1vbmV5?= <dmo...@discussions.microsoft.com>
GetSaveAsFilename

Can anyone advise me a method to divert/re-program the 'Save' button on the GetSaveAsFilename dialog to run my own comma delimited file saving routine, I need to save specific areas of text values and formulas from an Excel 2000 worksheet in XP, or will I need to recreate a facsimile of the file SaveAs form. Thanks for any help Oswald

3/10/2010 4:11:58 PM 1 Oswlad <wo...@kpk-sheetmetal.co.uk>
How to print with a gap in the middle of a sheet

I am trying to setup a print button with code to print a range of data on a 11x17" sheet of paper, portrait layout but to have a break in the middle of the sheet always so if you fold it in half you will not be folding over text. I need something like a footer in the middle of the page. I have 2 seperate parts of the data range. One is for first shift and the other is for 2nd and 3rd shift. So if you fold the 11x17 sheet in half you get 1st shift data on the front and then the 2nd&3rd shift info on the other half but with a break in the middle where the crease is so no text gets folded

3/10/2010 4:10:07 PM 0 Scott <sriddl...@gmail.com>
Conditional Formatting VBA Multiple Numeric Ranges

I have a worksheet with hundreds of rows of data in columns A through AV, and want to have the color of columns A through J ONLY change for each row based on a numeric value in column J. For example, if the value in column J <0, color = red, if between 1 and 30 = yellow, if between 31 and 45 = blue, if between 46 and 60 = green, if between 61 and 90 = purple, if >120 = gray...and so on. I'm using Excel 2003 unfortunately so am limited to 3 conditional formats and need to use VBA. Any suggestions on how to code this? Thanks!

3/10/2010 3:41:01 PM 1 =?Utf-8?B?c3RldmVkZW1vNzc=?= <stevedem...@discussions.microsoft.com>
Modeless User Form Is Blank

I want a modeless user form to appear, then my program does some work (clearing the screens of all drawing objects, which takes a while), and then the user form to unload. I've gotten this to work, except that the user form is completely blank and white. The label control with my message doesn't show. I've tried DoEvents before showing the form, repaint in the form initalize (as suggested in other posts on this topic), and this doesn't help.

3/10/2010 3:27:48 PM 6 Lucky <kingofwhi...@aol.com>
VBA-Protect Row

Hi All, I want to protect a row when a specific value is entered into a cell. For example, when column Final is changed from (blank or No) to Yes, that row is protected (in addition to the a function already programmed). {code for function already programmed ''''When Study Final is changed to Yes If Target.Column = 29 Then If Cells(Target.Row, 29).Value = "Yes" Then ''''Backlog set to 0 Cells(Target.Row, 54).FormulaR1C1 = 0 ''''Protect Row '????? End If End If } I've read enough posts statin

3/10/2010 3:08:01 PM 2 =?Utf-8?B?SmVmZg==?= <J...@discussions.microsoft.com>
How do I add numbers in two column based on the date in another?

Hi Please help. Using Excel 2003, I am trying to find a way to determine if a date changes in a range, total amounts in two columns, by date, by route otherwise return a blank. A=Route# B=Date N=Total Hours(of a call) O=Time Between Calls P=Total of N+O A B N O P 01 16/02/2010 1.25 FALSE 01 16/02/2010 1.17 0.167 01 16/02/2010 0.50 0.083 01 16/02/2010 1.25 0.083 4.503 01 17/02/2010 1.00 FALSE 1.00 I trie

3/10/2010 2:45:01 PM 2 =?Utf-8?B?TXlzYQ==?= <M...@discussions.microsoft.com>
Select range from unopened workbook

I'm trying to take data from an unopened workbook. I copied the following code directly from my "Excel 2003 VBA" book. range("A12") = Workbooks("BILLRATE.xls").Worksheets("BILLRATE").Cells(1, 1).range("A1") I get a "subscript out of range error on this. The workbook "BILLRATE" is in the working folder. And I can call it if I use Workbooks("BILLRATE.xls").Open. But when I try to access the range, I get the subscript out of range error. Any ideas? thanks

3/10/2010 2:24:32 PM 6 Andrew <andrewkgent...@gmail.com>
update in the workbook by different users

I have a worksheet shared by 6 people at sharespace. Please let me know how can I control the parallel updates of all in the sheet. e.g Let us say all users open up file at same time t. If user 1 make some change and save it at time t+1. Then if user 2 is saving it at t+2, updates entered by user 1 will be missed out. Please advise how can I manage this using macro or sharing control. regards sanjay

3/10/2010 1:37:05 PM 1 sanju <sun...@gmail.com>
Find Value in Workbook

I have the following VBA Code in the Active Worksheet to search for a value (cell J3) in column( D) then activate the cell if found. I would like to modify the code to search for the value in cell J3 in all worksheets in the workbook, then have a prompt to activate that cell in the sheet it is located or do another search for the same value. I realize I can do this by the Find function but this would work much better for my application. Thank you Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range

3/10/2010 1:36:01 PM 1 =?Utf-8?B?Sm9obm55?= <Joh...@discussions.microsoft.com>
Out of context error in user defined function

Apologies to anyone who has already read this in Functions. I realised after a few daysa of no response that I had posted in the wrong stream. Excel 2007 query Error received in VBA window (Debug) = <Out of Context> Error on Spreadsheet = #VALUE# I have 'formatted' this function in much the same way as my twenty or more other functions in this spreadsheet, yet I end up with Out of Context. I have tried various approaches including an 'On Calculate' called sub-routine, but all to no avail. The code for the function is stored in Module1 of an xlam, which is where all my o

3/10/2010 1:04:01 PM 0 =?Utf-8?B?UGhpbGlwIE1hcmsgSHVudA==?= <PhilipMarkH...@discussions.microsoft.com>
WorkBook Custom Properties

Is it possible to check a workbook's properties without opening it? If so, can a workbook's custom properties also be checked? Any code doing this would be appreciated. PWS

3/10/2010 12:08:40 PM 1 "Paul W Smith" <...@NOSPAM.twelve.me.uk>
Sumproduct formula needed

Data************************ A B=85..col 001 40000 002 25000 999 3200 005 11360 920 -3000 992 -165870 170 3290 210 31090 991 -9220 ******************************* Hi all, I need Sumproduct formula in cell C1 which should check those values in column A which starts with 9 and then do the SUM of values in column B. I tried formula (see below) but its not working. SUMPRODUCT((A1:A9=3D9**)*(B1:B9)) I can solve it by SUMIF formula but I want to do it in SUMPRODUCT formula. Please can any friend help me

3/10/2010 11:42:08 AM 2 K <kamranr1...@yahoo.co.uk>
Dependents problem

Hi all, I have an input cell that is data validated. It is the only cell currently selected. In VBA immediate (or in a module) if I check for ?selection.dependents.count it tells me the (1004) No cells were found. yet when I enter selection.showdependents it shows the dependency "button" and arrow. If I look at selection in the watch window, Dependents - (No cells were found) Directdependents - (No cells were found) Please can anybody shed some light on this? Kind regards, Bony -- "There are 10 types of people in this world. Those who understand Binary

3/10/2010 11:03:01 AM 5 =?Utf-8?B?Qm9ueSBQb255?= <bony_ponySPAMS...@BLOODYSPAMbtinternet.com>
How to reset the range name to refer to another cell?

I have set "G" as the a range. In the later part, I need to use "G" as a reference to set itself as another range. what is the correct syntex? Set G = Worksheets(E).Range("B21") .... Set G = Range(G).End(xlRight) <-----Error occurs here Please help!

3/10/2010 10:06:01 AM 3 =?Utf-8?B?VGVycnk=?= <Te...@discussions.microsoft.com>
Special problem deleting a folder

2003/2007 (This has to do with PowerPoint VBA, but I'm asking here because theres a lot more VBA users here.) Is there a way to run a batch-like command line to delete a folder from within routine without calling to an external BAT or CMD file? I'm trying to simply delete some folders within which is an addin that my code already unloaded and removed and unregistered, and now I want its folders deleted (within the same PPT session that started the macro). But PPT holds on to the addin file thinking it's still being used (which it's not) and won't let RmDir or Kill code remove it. (

3/10/2010 8:40:20 AM 3 Mel <mel.tu...@yahoo.com>
Call Stack Available ?

When browsing the Locals Window, I happened upon the call stack display. I looked in Help but could not find a way to access it with VBA. It might be handy in error processing in knowing how you got somewhere. If it's available, how do you access it? Thanks. -- Neal Z

3/10/2010 8:19:01 AM 2 =?Utf-8?B?TmVhbCBaaW1t?= <nealz...@yahoo.com>
Spliting Names

Hello, I have a sheet that in Column A names come in "X, Y" X as LastName and Y as FirstName. Is there a sample that I could split this column into to keeping LastName in Column A and after removing "," put the firstName in Column B? -- Jeff B Paarsa

3/10/2010 6:52:01 AM 2 =?Utf-8?B?SmVmZmVyeSBCIFBhYXJzYQ==?= <JeffBPaa...@Yahoo.com>
ShowDetail

When data is extracted using ShowDetail, the data is always extracted to a new sheet. Is there any way that this data can be extracted to a named range?

3/10/2010 4:51:01 AM 1 =?Utf-8?B?TGVzIEdvbWJhcnQ=?= <LesGomb...@discussions.microsoft.com>
Convert decimal digit to integer

Hi All, I have the value 1.0020 in a cell, I want to assign the second right decimal (2) to an integer variable, how do I do that? Any help will be very much appreciated

3/10/2010 2:52:01 AM 4 =?Utf-8?B?SG93YXJkMzE=?= <Howar...@discussions.microsoft.com>
Excel Crashes when trying to open a User Form

When I open the workbook and click on the worksheet to open the user form excels stops working. I get Microsoft Office Excel has stopped working and is trying find a solution, but it never does. I have saved it in both 2007 & 2003 versions and both get the same problem. What is odd is if you open the Userform Workbook, then open the VBA code window, both userform versions run fine, but if you do not open the Code window Excel locks up everytime. Here is the code. Sheet 2 Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) UserForm1.Show End Sub Us

3/10/2010 2:35:01 AM 3 =?Utf-8?B?QnJpYW4=?= <Br...@discussions.microsoft.com>
Need help with VBA help

I am using Excel 2003 with all updates. When I type a keyword into the VBA "Type a question for help" text box, I get a list of entries as normal. When I position the cursor over any of the entries, it turns into a hand with a pointing finget, indicating that the list item is a link. This is also normal. When I click on some of the items, I get a window telling me about that item as I should. However, when I click on other items, nothing at all happens. It seems that approximately one-half of all the items exhibit this behavior. I have tried reinstalling Excel but that does no

3/10/2010 12:45:44 AM 3 NoS...@aol.com
Unprotect Workbook with Function

I unprotect and protect all worksheets in a workbook with a User Defined Function that returns the password. Sub MyExample() ActiveSheet.Unprotect Password() ' do stuff to the worksheet ActiveSheet.Protect Password() End Sub Function Password() As String Password = "AdTech" End Function This works great! But for some reason when I try to unprotect or protect my workbook I get a Run Time Error. I know the password is "AdTech", because I can manually unprotect and protect the workbook, but VBA doesn't like to do it thru code. Sub MyExample() ThisWorkb

3/10/2010 12:30:02 AM 3 =?Utf-8?B?UnlhbiBI?= <Ry...@discussions.microsoft.com>
Why does column revert to date format after setting to numeric for

In a worksheet that I have been using for a few years I add daily rows of data to 2 new columns each year (the data in the new columns get compared against the data in the previous columns). One column (left column) is numeric and the next column is datetime (so for several years we have a numeric column followed by a datatime column and the following year is Numeric column followed by a datetime column ...). This year the numeric column keeps changing to datetime when I add a new piece of data to the cell in the next row and I have to keep reformatting it to numeric. Why doe

3/9/2010 11:34:01 PM 2 =?Utf-8?B?UmljaA==?= <R...@discussions.microsoft.com>
ComboBox and rowsource from an external sheet

Hello, I'm trying to fill a combo box from a column of names in another workbook. Here's what I have: menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100" This appears to be correct. I get an error code 380 - could not set row source property. Can someone tell me what is wrong with this code? thanks,

3/9/2010 10:21:43 PM 7 Andrew <andrewkgent...@gmail.com>
Structured Table Refs in VBA

I'm confused about the relationship between tables (using structured references) and ranges. I can work on individual cells from a table column, similar to working with a range, using- Dim oSh As Worksheet Set oSh = ActiveSheet Dim r As Range For Each r In oSh.Range("MyRange[ColumnName]") r.Value = 10 Next But if I try to create a range object from a table, using any of- r = oSh.Range("MyRange") r = oSh.Range("MyRange[#Data]") r = oSh.Range("MyRange[ rowvariable, [ColumnName]]") I think I am failing to see some basic principle here. I see

3/9/2010 10:01:02 PM 1 =?Utf-8?B?Y29saW5fZQ==?= <coli...@discussions.microsoft.com>
if / then structure with macros

Hi there, I am using a macro to count colored cells. You can see the code below. Then I use to count the cells with =SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35)) where 35 is the colorindex (i got it with =colorindex(a1) ) now i need to and an if statement:) something like, scan the range of F33:F285, find the cells containing number 3, check if these cells has colorindex 35. with a noob approch it'd be like =IF(COUNTIFS(Sheet1!B33:B285,3),SUMPRODUCT(--(ColorIndex(Sheet1!F33:F285)=35))) I thing I cannot figure out if/then structure right:( any ideas? thanx in advance.

3/9/2010 9:35:01 PM 1 =?Utf-8?B?bW5z?= <...@discussions.microsoft.com>
Manadatory break after 10 days of work formulae

Through the assistance of the exceptionally talented moderators/ contributors of this group, I was provided with the formulae shown below. It works to assisst me in scheduling crews of workers based on a 10-4 or (10 days on and 4 days off) or a 21-7 (21 days on and 7 days off) schdule. A new twist now requires that I modify this formulae slightly to include a mandatory 2 day break following 10 days of work. As such, the 10-4 schedule works just fine, but I need to have two "off" days appear after 10 working days for those people working the 21-7 shift. Thank you in advance for any

3/9/2010 9:11:19 PM 0 Scott <scott.ma...@gmail.com>
Manadatory break after 10 days of work formulae

Through the assistance of the exceptionally talented moderators/ contributors of this group, I was provided with the formulae shown below. It works to assisst me in scheduling crews of workers based on a 10-4 or (10 days on and 4 days off) or a 21-7 (21 days on and 7 days off) schdule. A new twist now requires that I modify this formulae slightly to include a mandatory 2 day break following 10 days of work. As such, the 10-4 schedule works just fine, but I need to have two "off" days appear after 10 working days for those people working the 21-7 shift. Thank you in advance for any

3/9/2010 9:11:07 PM 4 Scott <scott.ma...@gmail.com>
Reliable send keys

Hello I use the code below to open and run ipconfig /all to file. It woks fine on my laptop but is hit-and-miss on other laptops, sometimes running; sometimes producing Enter only; sometimes nothing more than the open cmd window. Is there a way to ensure the code does its job? I was thinking of While Dir produces nothing and shortening the wait time but the thought of the SendKeys disappearing into buffers or elsewhere worries me - running the code in break mode experience :-) Is there another way? Sub RunCMD() Dim ReturnValue sCmd = "ipconfig /all > " & ActiveWorkboo

3/9/2010 7:27:59 PM 5 "AltaEgo" <Somewh...@NotHere>
WorksheetFunction Calculation Error

errorWS.Range("B3").Value = Application.WorksheetFunction.CountA("A5:A" & errorWS_startRow - 1) The above line of code is always 1, even when errorWS_startRow is much > 1, i.e: errorWS_startRow=144, 749 etc. Any ideas what is going on?

3/9/2010 7:16:02 PM 2 =?Utf-8?B?QXlv?= <...@discussions.microsoft.com>
Error 1004-application or object error

Using Excel 2003. I'm trying to use VBA to select & open a csv file, search for specific text (using the data in Cell A1 of my wb), etc. However, I am getting a Run-Time error 1004. Where am I going wrong? The code in part is as follows: Sub GetFile() Dim FileName As Variant FileName = Application.GetOpenFilename If FileName = False Then Debug.Print "user cancelled" Else Debug.Print "file selected: " & FileName End If Call ReadCSV2(myFileName, SearchData, DestSht) End Sub Sub ReadCSV2(ByVal myFileName, ByVal SearchData As String, ByVal DestSht) DestSht = "sh

3/9/2010 6:36:04 PM 5 =?Utf-8?B?TC5NYXRoZQ==?= <LMa...@discussions.microsoft.com>
Chart datalabels linked to cells

I have an old Excel 2003 macro that duplicates a sheet with a column chart on it. The column chart has data labels that are linked to cells in the sheet. Problem: Now that we're in Excel 2007, the datalabels on the chart on the duplicated sheet still refer to the original sheet. So we want to adjust the macro so that the labels point to the same cells on the active sheet. Can someone just give me the syntax? e.g. Activechart.Seriescollection(1).datalabels(1).formula="=data!C3"

3/9/2010 6:34:01 PM 0 =?Utf-8?B?RGFuaWVsIEJvbmFsbGFjaw==?= <DanielBonall...@discussions.microsoft.com>
Command Button in Excel 2007 Chart

Hello, I have buttons in a spreadsheet that let the user go right to a particular chart. Each chart has it's own sheet. I need a button to go back to the spreadsheet called "INDEX". I had done this in Excel 2003 no problem...just stuck a button on the chart and Private Sub CommandButton1_Click() Sheets("INDEX").Select End Sub It will not work in 2007. It won't even let me get at the button once I've pasted it in. What am I doing wrong? Thank you in advance for any help you can give me. Gee

3/9/2010 6:30:01 PM 3 =?Utf-8?B?R2Vl?= <...@discussions.microsoft.com>
Modify code from paste values to change font color / multiple rang

Hi, I was wondering if you could help me out modify a code that currently paste values to change the font color to automatic or black and to add multiple ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81) The current code is below and works Great! Thanks to everyone that helped/will help me out!! Sub PVPrImpct() With Range("b48:b74").Offset(, Worksheets( _ "Summary by month MTD").Range("A4") - 1) .Value = .Value End With End Sub Thanks again!

3/9/2010 5:55:01 PM 3 =?Utf-8?B?TmluYQ==?= <N...@discussions.microsoft.com>
Excel TextBox Control

I have a textbox named "txt_report_date" inserted in a worksheet called "admin" When this worksheet is active I can populate the textbox value using the me syntax, me.txt_report_date = ... However I would like to set the value of the text box when the workbook opens. Can you please tell me how to reference the text box from the on open event of the workbook. Thanks.

3/9/2010 4:24:01 PM 2 =?Utf-8?B?SmFtZXM=?= <Ja...@discussions.microsoft.com>
Launching from IE

Hiya, I've built an Excel file with a user form that provides boxes to search our SQL database and populate the spreadsheet with the data it finds. This works absolutely fine when opening from a local or network drive, but when providing this on our intranet, the form works the first time, but when clicking the New Search button (which just invokes the form) I get VBA error messages. Any ideas? Thanks

3/9/2010 4:05:02 PM 1 =?Utf-8?B?QW5keSBTbWl0aA==?= <AndySm...@discussions.microsoft.com>
API License

Hi, I've developped an application in VB6 that uses the Excel API (Microsoft Excel 9.0 Object Library - Excel9.olb) I need to deploy this application on many computers (for many users) of my company who don't own a license for Excel. Is that legal? Or do I have to buy an Excel license per user? Basically my application will have to handle data contained in .xls files, that's why I need the Excel API. Thanks for your answers / links to any Microsoft page concerning the redistribution of Office DLL. (And if that's doable, which DLL would I have to include in my redistribution

3/9/2010 4:04:23 PM 8 "J�r�mie Gent" <jer_...@hotmail.com>
Need a accumulator driven from second cell

I am new to VBA, just got book yesterday. Found some code for entering on a column works get all by itself, but does not help with problem. Also found code for a single cell accumulator, also work real well all by itself. Need to marry these two codes, so that I input on column C and get an accumulative total on column F. Here are the codes: Sub ChkColC() If ActiveCell.Column = 3 Then If Not IsEmpty(ActiveCell) And ActiveCell <> "" Then MsgBox "Has Data" Else MsgBox "No Data" End If End If End Sub -AND- Dim Val Private Sub Worksh

3/9/2010 3:29:03 PM 4 "Jay108" <u58...@uwe>
Error 2007

Help!!! This macro used to work perfectly fine. Nothing has changed. All of a sudden, I am getting a type mismatch(error 2007). I don’t know how to fix this. Sample of my code: Range("A4").Select With ActiveSheet.PivotTables("PivotTable4").PivotFields("ST-LOC-NUMB") .PivotItems("(blank)").Visible = False End With Range("A1").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[2]C:R[9998]C)" Range("A1").Select Selection.autofill Destination:=Range("A1:M1"), Type:=xlFillDefault Range("A1:M1").Select Range("A2").Select ActiveCell.F

3/9/2010 3:28:02 PM 0 =?Utf-8?B?bWp1?= <...@discussions.microsoft.com>
vLookup with comma break

Greetings all, I have a worksheet where cell B8 and B10 perform a vLookup based on a data validation in B6: =IF(B6="","",(VLOOKUP(B6,Position,2,0))) IF(B6="","",VLOOKUP(B6,AD_Groups,2,0)) I have the following code in place to perform a line break after each comma in the returned value. It works great the first time, but then anytime I change or add something to any cell in the range B1:B10 it adds another line break to B8 and B10. Anything after B10 is fine and doesn't affect the previous cells. I guess what I'm asking is if there is a way to alter this code (which I fo

3/9/2010 3:16:01 PM 0 =?Utf-8?B?Sm9uIE0u?= <J...@discussions.microsoft.com>
Comparing and updating sheets.

SUB MACRO_RUN Call neha1 Call neha2 Call neha4 Call neha5 Call neha6 End Sub Function neha1() For B = 2 To 50 For a = 3 To 50 If Worksheets("Activity").Cells(B, 4).Value = Worksheets("IP_MPLS").Cells(a, 1).Value Then Worksheets("IP_MPLS").Cells(a, 32).Value = Worksheets("Activity").Cells(B, 10).Value Worksheets("IP_MPLS").Cells(a, 21).Value = Worksheets("Activity").Cells(B, 1).Value Worksheets("IP_MPLS").Cells(a, 22).Value = Worksheets("IP_MPLS").Cells(a, 22).Value & ". ;" & Worksheets("Activity").Cell

3/9/2010 3:14:44 PM 3 sanju <sun...@gmail.com>
If statement in macro to find blank cell/value in another cell

Trying to find a macro that will allow me to find a blank cell in a column, and if the corresponding cell is populated, then tag that cell with a certain value. So, if Column AG is empty, but Column AM is not, then put an * in column AG, then loop it to look in other columns as well. So if AG = blank, but AM is not blank, then place * in AG, if AN = blank, but AT is not blank, then place * in AN, if AU = blank, but BA is not blank, then place * in AU, if BB = blank, but BH is not blank, then place * in BB, etc.... For a total of 15 segments

3/9/2010 3:14:02 PM 1 =?Utf-8?B?S2VubmVkeQ==?= <Kenn...@discussions.microsoft.com>
Problem with Worksheet_SelectionChange

I have a problem with the following code. I've cut it down to remove a bunch of conditions defining rTick, but the problem I have is with the actual code to change the cells. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '----- ENABLE TICKS IN RELEVANT BOXES ----- Application.EnableEvents = False On Error GoTo sub_exit With Worksheets("Sheet1") Set rTick = Worksheets("Sheet1").Range("AE9:AE10,Q11:S13,Q22:Q25,S30:W40,AA17:AE38") '--------Problem with this bit!! If Not Intersect(Target, rTick) Is Nothing Then With Target.Cells(1, 1)

3/9/2010 3:10:15 PM 0 "IanC" <...@me.com>
Excel 2003 VB referencing #N/A not working but works in Excel 2007

I have a very simple macro that works in Excel 2007 but hangs up constantly in Excel 2003. Does anyone have a work around? Problem: When referencing cells that have a #N/A value, Excel 2003 craps out even if I use the ISNA application function before it. The following 2 statements will cause Excel 2003 VB to stop regardless of order if the cell that is referenced comes up with #N/A. Series = Cells(RowNumStart + X - 1, SeriesCol).Value If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then Further background.... I am actually wanting to find #

3/9/2010 2:47:01 PM 1 =?Utf-8?B?S01I?= <...@discussions.microsoft.com>
Custom XLAM tab not showing up when excel is opened from word

I have two closely identical add-ins for word and excel; the word dotm is saved in the startup folder for word and the excel xlam is saved in the addins folder. They have been working fine for about a year and a half. I added some more functionality to them both by giving the ability to open an excel sheet from the custom ribbon tab and pre-populating it with information from the document that it was opened from. When the beginning document is excel, it works fine which makes sense. when the beginning document is word, however, the macro works correctly and opens the excel do

3/9/2010 2:46:01 PM 0 =?Utf-8?B?Y2hyaXMgc2VpdGVy?= <chrissei...@discussions.microsoft.com>
How to add a worksheet from template without duplicate cell styles

I need to add worksheets based on worksheet templates to a workbook. I would like to use the cell style "normal" as defined in the workbook. However, all cells in the added worksheet with style "normal" now have style "normal 2" and will not use the formatting set in the original workbook. Is there any way to get around this without going through all cells in VBA and changing the style?

3/9/2010 2:42:01 PM 0 =?Utf-8?B?WmVx?= <...@discussions.microsoft.com>
Retrieve part of a calculation

I would like to scan a range of cells and if there is a calculation in the cell, such as =5000 - 200, I would like to replace the value of the cell with what is immediately to the right of the equals sign. In this example I would like the new value of the cell to be 5000. All of the calculations will be in the format of =n1 - n2, I need to get the value of n1 and place it in the cell. If there is not a calculation in the cell, I want to leave it unchanged. Can someone give me the logic to do this? Thanks

3/9/2010 2:26:29 PM 5 slowjam4 <slowj...@gmail.com>
update sheet with data at bottom of present data

I have 2 excel 2003 workbooks. Workbook one has 5 column with headings. Workbook 2 has same five colums with headings. 2 gets updated every month with data in A2:E5 (under the headings). I need this data range to go into the workbook 1 at the bottom of the last entry. This is 4 rows and five columns of data. I Need it to update only when called to update.

3/9/2010 1:42:01 PM 1 =?Utf-8?B?b3V0cmlnZ2Vy?= <outrig...@discussions.microsoft.com>
Freezing Header Row

Hello, I am trying to freeze the first row of an Excel Worksheet using VB. I have tried many different codes and none of them work, I have been getting Null Exception Errors. Here is a piece of code that I am trying to use to freeze the first row. objxloutsheet = objxloutwbook.ActiveSheet Dim FP As Excel.Window objxloutsheet.Range("A1, Z1").Select() FP.FreezePanes() = True I am also not sure on how to select the first row in the FP.FreezePanes() = True line. Any help would be appreciated, Thanks in advance.

3/9/2010 1:29:01 PM 3 =?Utf-8?B?bWR1cA==?= <m...@discussions.microsoft.com>
Userforms with CommandBar Popups

I've reposted this item as I answered the first post on this subject myself and think therefore it may have got lost in background clutter. I have created a class module that displays a treeview within a userform. The treeview can display several data types. For example, the user gets presented with a tree of service providers which are indexed uniquely by name and country. If the user chooses to add a new service provider, then he will be presented with a second userform ( a second instance of the same userform) that contains a treeview of the countries they may choose from. Ea

3/9/2010 12:23:42 PM 5 Neil <nmnaj...@googlemail.com>
Link Drop Down list with Pivot Table

Hi I'm using MS Excel 2003 and need a solution regarding link my drop down list with my pivot table. Query : I have created a drop down list in which month can be selected and wanted to link with pivot table in which month wise team count is displayed for a selected month. Presently I'm drag n drop the month in pivot table and its shows the count. But this exercise I have to do every time when I need the report. Is it possible that I have select the month from my drop down list (created in same sheet) and pivot table shows the data related to that month. Please help

3/9/2010 12:01:01 PM 3 =?Utf-8?B?Tml0ZXNo?= <Nit...@discussions.microsoft.com>
Inconsistent behaviour of attributes accessed through structured r

I have a table that represents information in an outline structure. The table has two columns, "Level" and "Requirement" that look like this- Level Requirement 1 Top Level 2 Next Level 2 Next Level again 3 Further sublevel 2 Back to Level 2 Now, what I want to two functions that will- 1) Set the indents of the "Requirements" column based on the level numbers, or- 2) Se the Level numbers based on the indents of the Requirements column. Sounds easy, And one way it is! Can anyone explain why this works perf

3/9/2010 12:00:01 PM 0 =?Utf-8?B?Y29saW5fZQ==?= <coli...@discussions.microsoft.com>
UserAccounts.CommonDialog - How it works

Sure would oblige a message box returning the full path of a file browsed. -- Thanx & Best Regards,

3/9/2010 10:41:01 AM 1 =?Utf-8?B?RmFyYXogQWhtZWQgUXVyZXNoaQ==?= <FarazAhmedQure...@discussions.microsoft.com>
reading data from .xls file

Hi, My boss "ask" me to prepare so called application, which will be able to create report about certain data. Some people are working on their files /.xls/ which consist of lots of data, but I only need to get from those files only certain data /e.g. name of customer, date, number of order, quantity/. I don't want to open their files /using vba/ to copy data because it's too long /and I don't know when my cooworkers are using their files/. The question is - is there any method to read /retrieve/ data from that files without openning them on my computer ? thank in advance

3/9/2010 10:38:54 AM 1 "Robert T." <rtomaszew...@spam.o2.pl>
Excel instance isolation problem

Excel 2003, sp2 Hi, I'm using an add-in to create a report. The report is put together with data from a few named ranges in a hidden excel worksheet. Below is the relevant code that's in a standard module in the add-in. Dim wkbData As Excel.Workbook Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sFileConnectionString As String Dim sFilePath As String Set wkbData = ActiveWorkbook sFilePath = wkbData.FullName sFileConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & sFilePath & ";Extende

3/9/2010 10:07:12 AM 0 "PO" <h>
Manipulate filtered data

I have a shortage report generated daily by the company server. It has PartNo (Col A), Owner (Col B), Comments (Col C), Qty (Col D). Part numbers will only appear in this file if qty falls below certain value. I already have a macro that can filter the daily shortage report by owner (which is my name). Based on the filtered list (all part numbers that belong to me), I will update the comments column. Now I created a master file with all the part numbers that I need to take care of. It has 2 columns: MyPartNo (col A) and Comments (col B). I also created a macro that can m

3/9/2010 10:01:01 AM 3 =?Utf-8?B?Y2hvbw==?= <c...@discussions.microsoft.com>
SFTP

Dear All Is it possible to use VBA to save to an SFTP site? - I have code to save to a usual FTP site just not too sure about SFTP. Any help greatly appreciated, Jason.

3/9/2010 9:34:37 AM 1 WhytheQ <whyt...@gmail.com>
Password for running Macro

I need to hide and unhide some columns using a macro and then prompt for a password when unhiding. I used the protect worksheet function and set a password, and this worked fine. My user has now informed me that the protect worksheet will already be in use..... so now I just need to have a macro that hides some columns, but the unhide is prompted by a password. Is this possible without using the protect worksheet function?

3/9/2010 9:31:18 AM 2 "Jim" <zj...@live.co.uk>
Timer to detect idle time?

If no changes are made to my workbook after 5 minutes, I want to autosave and close the workbook. Is something like this possible? thank you

3/9/2010 9:22:41 AM 1 "Robert Crandal" <nob...@gmail.com>
Adding daily updates to other master sheets

Hi Please can nayone help me in following There are an activity sheet which will contain update related to orders in coulmn D. I need to tranfer the updates column H and Column J of this worksheet to different worksheet as per match found in column D. Column J entries need to be replaced in other sheets which I am able to do as per macro_run. For B = 2 To 50 For A = 3 To 50 If Worksheets("Activity").Cells(B, 4).Value = Worksheets("IP_MPLS").Cells(A, 1).Value Then Worksheets("IP_MPLS").Cells(A, 32).Value = Worksheets("Activity").Cells(B, 10).Va

3/9/2010 8:50:41 AM 0 sanju <sun...@gmail.com>
Enter event in textbox control?

Is there a way to determine if/when the Enter key is pressed inside a textbox control?? I only have one textbox control on my userform....so, if a user types in text they will usually press the Enter key to indicate they are done typing. That is why I need to determine when/if Enter is pressed. thank u

3/9/2010 8:45:57 AM 1 "Robert Crandal" <nob...@gmail.com>
Situation when file is "read only"

My workbook is used in a networked environment by 5 users. If one user opens the workbook, that workbook will appear as "read only" to the remaining 4 users. So, if one of the 4 users opens this file in "read only" mode, I want to inform them who currently has write access to the file. Are there any VBA functions which return the username or user ID of the person who has current access to the file?? Thank you!

3/9/2010 8:29:25 AM 0 "Robert Crandal" <nob...@gmail.com>
How to code macro to import web link and avoid frame within HTML?

Does anyone have any suggestions on how to import web link into Excel and avoid frame on web page? When I browse following link, it look fine on IE, but when I insert this link into excel, it shows nothing, because of frame within HTML. http://www.hkab.org.hk/DisplayInterestSettlementRatesAction.do?Submit=Search&lang=en&year=2010&month=2&day=3 Does anyone have any suggestions on how to solve it within Excel? Thanks in advance for any suggestions Eric

3/9/2010 4:50:01 AM 0 =?Utf-8?B?RXJpYw==?= <E...@discussions.microsoft.com>
Last used row within filter

Lost of threads here mentioned about how to get the last used row but I couldn’t find one about the last used row within a filtered range? Say my used rows are from A1 to A20, row A20 may or may not show up after filter, what is the code to get the 20 as variable? Any idea please? Regards

3/9/2010 4:25:01 AM 5 =?Utf-8?B?U2Vla2Vy?= <See...@discussions.microsoft.com>
VBA code to paste data based on condition

I'm new to VBA programming and would appreciate some help with a macro. There is a database table that can be refreshed throughout the month that simply overrides the cell value from the same refresh. So the values update every time you refresh in the same cell until the next month begins (Date + Year are the column headings). What I need to do is break each month down into weeks like this (1-7 = Week 1, and so on where Week 5 is any day after the 28th of the month). I need a macro where the morning a new week starts (Day 8), I can copy/paste values the entire column in the

3/9/2010 3:26:32 AM 0 "Phi0129" <sauza...@hotmail.com>
compare value of cell in one sheet to range of another sheet

Still learning how to use VB coding to it's most effectiveness... I have two sheets. sheet1 and sheet2. I want to pull the value of cell(2, "d") from sheet1 and compare it to every value of column "d" in sheet2, highlighting as it goes by. Once it has finished I want it to go to cell(3, "d") in sheet1 and do the same thing, comparing itself to all the values of column "d" in sheet2. I know how to pull the variable I want, I know how to do the highlighting code, and I know how to do the loops and whatnot. What I do not know how to do is referance seperate sheets. help??

3/9/2010 2:13:27 AM 1 Matthew Dyer <matthew.e.d...@gmail.com>
Calculations not keeping up with macro

Hello, I have a spreadsheet with 51 tabs. Tab_1 is a summary of data contained on the remaining 50 sheets (such as averages and sums). A macro iterates through values of 1 - 100. During each iteration a variable with dependents changes on each of the 50 sheets and resulting data for each iteration is output on the summary tab (output such as the averages and sums for each iteration). The macro works great when I step through it, the problems occur when I run it at full speed. The output data at each iteration does not keep up with the macro, thus the outputs are inaccurate. I

3/9/2010 1:08:18 AM 3 "...@lf" <bribr...@gmail.com>
Responding to Keypress event on a sheet

I want code to respond to the keypress event on a worksheet. How can i do that?? Actually i need a code that runs a predefined when some key is pressed while the sheet is active. In particular, i want to disable the enter key (ie. the selection of cell should not shift to the next cell) and then it should run a macro. Thanks in advance.

3/9/2010 12:53:34 AM 1 Subodh <getsub...@gmail.com>
using formula =sum('*'!A1) in a macro does not work.

I am trying to use the formula above in a macro that is ran once a button is clicked. the problem is that the formula goes from =sum('*'!A1) to =sum('*''!A1'), this results in an error message (#NAME), does anyone know why this is happening when it is used in a macro? If i use this in a cell and type it myself it works great! Thanks in advance, TG

3/9/2010 12:23:01 AM 2 =?Utf-8?B?VEc=?= <...@discussions.microsoft.com>
val() is not work in my code

wrote the below code and i'm haveing problems when i get to: " StrAStart = CInt(Val(Left(SltArea, StrM))) StrAEnd = CInt(Val(Right(SltArea, strL - StrM))) " part of my code, it looks like it is when i get to Val. When the program gets to these lines it doesn't put anything for variables StrAStart and StrAEnd. Varaible SltArea will contain something like "$A$35:$J$43". I am trying to get the starting, and ending row that was pasted by program. If you know a way to fix my code to make this work or even a better way to get the info i need, it would be greatly appreciated. Pr

3/8/2010 11:24:01 PM 1 =?Utf-8?B?TXIuIEdldFJpZ2h0?= <MrGetRi...@discussions.microsoft.com>
VBA, refrencing a range

Hi everyone, I have the path to a chart or a range in Excel ( using link obk=jects sourcefullname) C:\WINDOWS\Standard\Desktop\Book10.xlsx!Sheet1![Book10.xlsx]Sheet1 myChart or for a range C:\WINDOWS\Standard\Desktop\Book10.xlsx!Sheet1!R5C2:R7C2 how can I refrence these in Excel VBA ? I mean I want to open these files and copy the specififc chart or range ? -- Best regards, Edward

3/8/2010 11:13:01 PM 0 =?Utf-8?B?RWR3YXJk?= <Edw...@discussions.microsoft.com>
Distorted display in spreadsheet apps

A recent issue (2 or 3 months) has become a real nuisance. In several spreadsheet apps I have designed, when data is entered into a cell on a worksheet, the display is distorted with a row from the 1st two worksheets. If I scroll out of range and return or if I deselect the sheet and come back to it the display returns to normal. This is happening to dozens of other users as well in multiple locations in my company. My IT dept says this could not have resulted from any network change. I have been designing these apps for many years but this distortion has only recently occured. T

3/8/2010 11:04:03 PM 1 =?Utf-8?B?U2Ft?= <...@discussions.microsoft.com>

Pages: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42











Newest Articles

lastlogon (time format)
5 min. 24 sec. ago

change date format to excel format
11 min. 30 sec. ago

error
14 min. 4 sec. ago

Select specific worksheets & copy - code problem
14 min. 30 sec. ago

Debugging SharePoint workflow in production environment
16 min. 5 sec. ago