Answer Questions

• Peter Ritchie vlookup

  Hello can anyone help me with how to write the excel vlookup function in vba the function in excel looks like this VLOOKUP(O2,'[Consolidated list of supplier.xls]Sheet3'!$A$5:$F$218,6,FALSE) how can i get something similar working in a macro. thanks namrata hello,   the whole code that i have written is as follows   Public LookupValue As Range Public TableArray As Range Public MyPath As String Sub VlookUp_Click() MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls" Set LookupValue = Sheet3.Range("O2") Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281") End Sub   namrata If the file is open then you d ...Show All

• z-one Excel Macros/ Protection / HideUnhide VB command

  Here is what I am doing I am using the HideUnhide command to HideUnhide rows. Here is a simplified version of what I am doing I have rows 1-20 and let's say when I input the number 5 in a specific cell (let's say A1) I want rows 15-20 to hide. This is easy and I can do it, but when I lock any of the cells on rows 15-20 and run macros, then it gives me an error. It seems that since the command HideUnhide does not have priority over lock cells, so it doesn't allow me to do it. In other words, if I lock cell A18 I cannot use the HideUnhide command because it would send an error. Is there a way to do this I have asked many people but noone seems to know how. Pierre ...Show All

• KeeperMustDie LCS/Office Communicator.. add contacts to users Communicator at Login (script)?

  I'm trying to use the lcsaddcontacts.wsf command to add contacts (is there another way to do this) I'm using the format of the command lcsaddcontacts / usersfile:users.txt and /contactsfile:contacts.txt I'm assuming I need both files... The users.txt file contents is like this: sip:userA@domain.local While contacts.txt is: sip:testuser@domain.local For some reason the script fails with an error stating "invalid class" at line 104. (In vista, or in XP, it just runs but no contacts get added).. I'm assuming the users.txt represents who I want to add the contacts to.. the sip:userA should match the LCS login name or the AD login Also.. does anyone know of a way to install the client and have the ...Show All

• Trainwreck Load userform returning variable not defined error (91)

  Hi there, I haven't previously had any problems loading/hiding etc userforms in vba, but I've just finished a project that runs fine when stepped through. However, when run properly, it returns a variable not defined error on the Load xForm command. The form is in the same templateproject and the load form command is the first to be executed. Any ideas Thanks in advance This code runs on the form's initialize event: frmWeb.WebBrowser1.Navigate (" http://wokiisp.net/index_res.asp ") frmWeb.WebBrowser1.Document.all("ServiceID").Value = 71 WebBrowser1.Document.all("ServiceID").FireEvent ("onchange") While WebBrowser1.Busy DoEvents Wend ...Show All

• Eusebiu picture will not load with mousedown

  Hi, I started this as a titled thread "problem with mousedown event". A couple of people have responded but no one seems to understand what's going on. It's kind of odd as this has got to be a simple problem! Here's the skinny: When I click on the mouse on part of the picture a new picture is suppose to load. In the demo case I clear the old picture out. If you click in the center of the picture or frame the original picture is suppose to load. The code sort of works by doing what I want if I move the mouse cursor off the picture and then back onto the picture. Isn't there some way to make the picture load without having to move the cursor I suspect that there's a basic mistake in how I set up the control. Here's the code that's ...Show All

• Steve Jensen Page Setup in Excel 2003 using VBA

  I can get as far as bringing up the print preview screen using VBA. How do I then get VBA to squeeze my spreadsheet onto one page before printing. There is probably a simple solution out there but I'm currently teaching myself VBA by trial and error... but my boss seems to think I know what I'm doing!!! Assuming all the data you want to print on a single page exists on Sheet1: Sheet1.PageSetup.Zoom = False Sheet1.PageSetup.FitToPagesTall = 1 Sheet1.PageSetup.FitToPagesWide = 1 That should do it. (I won't tell your boss if you don't!) ...Show All

• Deepak Hota hidden versus visible in pivot tables

  I am trying to determine the number of rows and columns that are viewable in a pivottable. As the source data changes, new values will be displayed so the dimensions of the table range will change. Is there a quick way to get the viewable rows and columns My objective is to define a chart using the internal datarange of the pivot table and by-pass the autoPivotChart behavior in order to retain control over the structure and format of the chart. Note: this is not the same as visible as when you restrict the pivottable using a Page value, not all potential items will be viewable but their pivotitem.visible property will be true. Solved. I did some more research of the object model and found the addre ...Show All

• robben07160 Prepare a list in excel for printing

  Hi, I have a two column list in excel with about six thousand entries. I want to be able to print it out in four columns. I am trying to write a macro that would go down the list for 47 rows and print it into the first column and then go down another 47 rows and print it into the next column etc. After the first four columns are done I want to offset the printable list down about five rows and do it all over again. I just can't seem to understand the range and offset routines to help me do this. Can anyone give me a quick looping macro Would appreciate it. Thanks (and an email to me at nsheff@aol.com would help) I'm not sure how easily 6000 rows will come across, but I'd certainly try copying or l ...Show All

• mikenwood How can I run sql command with no cofirmation alert through VBA

  hi guys i want to delete a record from another table in After-Update event of a text item in ms access form. the problem is the confirmation alert asking whether u want to delete or not. i don't want this alert to be displayed and I want to delete the record anyway. The code i'm using is docmd.runsql "delete from discarded where sr_no=' " & Form_INV.sr_no & "'" plz tell me how can I bypass this alert Thanks Khawar Pasha khawarpasha@hotmail.com How can we modify this to store the Warnings status first This code would tur ...Show All

• iamdon Registering as ISV

  In reading the documentation about registering for the empower program, I am a little unclear about what is mean by "clearly provides valid contact information". Is all that is required a corporate email address or is more required. Kind regards. All you need to do is make sure your contact information is accurate so you can receive communication. If you have any questions about the Empower Program, visit http://www.microsoft.com/partner/isv . Lisa (ISV Buddy Team) There is nothing complex here. Just provide all your contacts - valid email, postal address, phones etc. If you provide an incorrect email, you will not be able to receive any information from Microsoft. ...Show All

• _brg_ Combining Control Sources for Use in Reports

  Hi All, My situation: I have two fields in a table: FirstName and LastName. I want to put them in a report, but not be seperated in to two seperate text boxes (e.g. the fields may look like this: [John ][Smith ], with padding because the text box is a pre-defined length), but rather one box (e.g. [John Smith ]. Does anyone have a suggestion as to how I could get it two together in one field for view in Print Preview in a report Thanks in advance, Mat Hi Add a text box to your report ( which will be unbound) then in control source type in, or use the builder, =Trim$([FirstName]) & " " & Trim$([LastName]) ...Show All

• CodeDigger I need a looping vbcode to use in excell

  Hai, I need a vb code to be used in excel to make a table by the click of a cammand button and the details are under: I hava an address list arranged vertically. First cell has the serial Number and the cell right to that has the Name, next bottom row has the address line-1, next bottom row has the address line-2, then line-3 and then line-4. as shown below (Sl. Numbers are in column A and the Address lines are in column B) 1 X Industries 20/410, Appartment Lake view Lane Cochin 688625 After the gap on an empty cell the same way as above the address continues... may be upto 200 or 300 address by the click of a cammand button I want these addresses are to be arranged in horizontal ...Show All

• enric vives Log in with Active X

  Hi! Every night I have a macro that is supposed to run and open and login to a program (the Bloomberg professional) and download some data. My problem is the log in process from my macro. Now I use SendKeys but that seems VERY unreliable. Does anyone have any links with code that shows how to use some better techniques e.g. Active X to log in to a program Any help appreciated! Thanks a lot in advance! Hi AndersBank, Thats a difficult question to answer as I am not familar with Bloomberg Professional, however I had a look on Bloombergs site and there are all sorts of API's and addins available. There is an Excel addin here.... http://about.bloomberg.com/software/info_api.html Is ...Show All

• toratora Displaying the description of a function and each of the variables within "function arguments" window.

  Dear all, When I want to insert a function into a cell, I press "insert function", I select the function that I need from the list, and a new window is displayed with a brief description of the function, and also the description of each of the variables in the function. How can I add this descriptions for my own functions Thanks! ...Show All

• Mitch Walker - MSFT Changing back color of a field based upon another field

  I am interested in changing the back color of a field based upon a second field's value. This field is contained within a continuous form. All that seems to happen currently is every occurance of that field changes its color. Each field has a different value, can this be accessed for the formating as well The problem with a continuos form or a datasheet, is that whatever is done within one record, that entire column received that same formatting. UNLESS you do it with Conditional Formatting. You can get to Conditional Formatting by right clicking on the field you want to add conditional formatting to. The read the information on the dialog box that comes up and make the changes you want. These formatting ...Show All