VBA and Excel help

I'm not a newbie at this but it has been so long I feel lost like one

I need to construct a routine for the following:

I have created a travel voucher form that has a 1300+ list of data of
virtually every city in the country, many with multiple entries such

WILLIAMSBURG, VIRGINIA, 1 Apr through 31 Aug
WILLIAMSBURG, VIRGINIA, 1 Sep through 31 Mar

Using data validation (list) as an input method, for each day traveled
(from 1 day to 4 weeks, where each day can be a different location),
the user inputs the travel location by clicking on the data validation
list box drop arrow in the cell that matches the travel day and selects
the travel location from the list (it uses a VLOOKUP table to output
the $$ he gets for that location and time of year).

However, the data validation method, while easy to implement, is
limited in that
(1) you can't see the entire line because the input box is
width-limited to that of the selected cell which is "wrapped"; the list
box cannot do that and
(2) the user can't go straight to the city by typing but rather is
limited to scrolling only.

I would prefer to build a entry method that:
(1) shows the whole width of the entry;
(2) be able to begin typing the entry (i.e. "w"..."wi"..."wil" etc) and
be taken straight to the area of the list (and with the more I type the
closer I get) to quicken my input
(3) places the result in the same cell from which I ran the routine,
(4) is initiated as easily as a validation box (a command button just
above each possible input cell?)

I would think this can all be done easliy enough with a combo box but
since it has been years since I have tried to build a form-based

Any help would be appreciated!


rdillon (14)
1/11/2005 9:02:14 PM
excel 39879 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 29


Similar Artilces:

More help re: entry on 22/2/04
Thanks for help re: creating vertical lines on a stacked bar chart. The site suggested was very helpful. However it didn't explain how to work out the values for the blank cell placeholder values so that the line went from the first bar to the last bar (I have 20 stacked bars). Can you help?? This was the site that helped me: http://www.peltiertech.com/Excel/Charts/BarLineCombo.html Thanks. Kaixi - If the attributes are being used as simple categories in the chart (most likely), Excel uses integer values 1, 2, 3 etc behind the scenes while making the chart. In a column chart ...

Need help to call Compact & Repair Database command from a office VB script
I have a VB script in Excel that runs something like SET db = opendatabase ("Filename") db.querydefs ("Run query 1" etc... What I'm doing is running a query to delete the data in the table, drop the indexes, load the data and then rebuild the indexes. I'd really like to throw in a compact and repair database as well to avoid the 2 GB data limit and the subsequent "invalid parameter limit". Can someone guide me how to run the compact database command please? Thanks. Public Sub CompactDatabase() On Error Resume Next dao.DBEngine.Compac...

why wont an excel doc open with new msn office
i have just got a new comp with the new microsoft woeks on it, word doc if differant, and a few other things but an excel doc was sent to me an it will not open it ???????? Hi MS Works is a 'light' version of Office, containing a 'light' version of Excel and a 'light' version of Word. You cannot open Excel or Word files with Works. You'll have to get the files saved as .csv or .txt or a Works file to be able to open them with Works. Andy. "peterk4" <peterk4@discussions.microsoft.com> wrote in message news:53597904-F529-4E67-BC56-D957C375779...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

Excel 2002 Create Workbook (merge/reformat)?
I have 12 seperate spreadsheets I want to combine into 1 workbook (12 tabs). Any other alternative ways to do this other than copy and paste? Need to reformat "most" of the worksheets, I assume this can be done if all 12 tabs are selected. I may have to do this several times otherwise I would just copy and paste. Each spreadsheet has an average of 3k-4k transactions. You can open the workbook, ctrl-click on the worksheet tab and drag that worksheet into another workbook. (Then close that "sending" workbook and open the next...) ~Dave~ wrote: > > I have 1...

Excel 97-remove link but keep data
Hi guys, I have a pre-existing excel 97 s/s which has links to other s/s's. I would like to keep the data only (like paste-special, values) and remove the linked reference from showing in the current s/s. Any ideas how to do this instead of me changing each linked reference? Thanks in advance! search for .xls or [ or ] to see if you can find the formulas that contain links to other workbooks. Van wrote: > > Hi guys, > > I have a pre-existing excel 97 s/s which has links to other s/s's. I would > like to keep the data only (like paste-special, values) and rem...

Excel file takes a long time to save
I have a 1,000 x 15 straight worksheet with no formulas - about 100K. I went to auto-format a pattern, but didn't select the area and if formatted the entire spreadsheet. It's since gone from 5 seconds to 60 seconds per save and I can't reverse this, even by changing the auto-format setting to "none." I'm backed-up, but the current version is about three hours ahead and it will take longer to retrace the work by hand. Any suggestions? Thanks, Ben Try resetting the last used cell. Debra Dalgleish has some techniques at: http://www.contextures.com/xlfaqApp.html#Un...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

Excel design questions
Hi, I'm a new to excel. I'm assigned task to perform creating charts based on the following data. I explored little bit from web about the capabilities of excel, but few areas I'm not still quite sure about how to get it working. - Is it possible to have advanced filter to automatically refresh if the data is changed on which the filter is acting on?? Do I need to write maro for this?? - If i have a column with data (different types of tree names, ex: Apple, Apple, Maple, Grape,Grape..etc - name is repeatable). Can I uniquely get the name of all the different tree names automati...

PopUp Form Help
I'm trying to create a pop-up form to allow users to enter data to table that main form is based on. Due to size of main form and because pop-up fields will only be used per certain entries on main form, I want to use a pop-up form to enter needed data. On the main form, I have the fields listed below. For example, if user enters "TypeOne" as a PType and then enters Model, AfterUpdate to Model field a pop-up form opens with fields for UnitGroupName and UnitModelName. I would like the UnitGroupName to be populated with "All TypeOne Products" when the po...

Excel 2000 #9
Is there a way to change the date format in footers and headers.?.these two items do not seem to pick up the formatting specified for the individual cells. David H. David Excel in any version uses the Short Date format from Windows Regional settings in headers/footers. You can make some minor changes in Regional settings but not too much. How about a macro to add the date in any format you choose? Sub DateInFooter() With ActiveSheet .PageSetup.CenterFooter = Format(Date, "dddd, mmmm-dd-yyyy") End With End Sub Gord Dibben MS Excel MVP On Tue, 5 Feb 2008...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Multi-User Excel File on a Network Drive
How can I make an Excel file accessible to multiple users concurrently? With all able to make and save changes to the same file. Thanks, Mike "Mike" wrote > How can I make an Excel file accessible to multiple > users concurrently? With all able to make and save > changes to the same file. Tools / Share Workbook Jordon ...

Inserting Rows in Excel
I have a spreadsheet with a unique identifier for each customer, Customer Information File (CIF). Whenever this number changes, I want to automatically insert a blank row. This will merely making the the spreadsheet more pleasing to the eye to read. Keep in mind, there may not be a break in CIF for 1 to 15 lines, but whenever this CIF number changes, I want a new blank row. Any help would be greatly appreciated!! David David Macro solution OK? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With ...

please help #3
In Money 2003, when I receive payment on an invoice, it automatically records it to my register. Is there any way around this? The reason is that I like to make one large deposit, and I want this large deposit to show in my register, not the individual ones. Thanks!! .. ...

Help monthly sales by person
Need formula to figure amount of sales per person in a given month. columns currently are: Inv#, Date, Sale, Net $, Salesman # Need formula for monthly sales per Salesman # Thank you -- Htoomuc Posted from - http://www.officehelp.i With your headers in Row1, from Columns A to E, And the datalist from A2 to E100, Enter the month and year to lookup in F1 (mm/yy), And the salesman's # in F2, And try this: =SUMPRODUCT((TEXT(B2:B100,"mm/yy")=TEXT(F1,"mm/yy"))*(E2:E100=F2)*D2:D100) -- HTH, RD --------------------------------------------------------------------------...

Use Excel to update Outlook address book
hi all... Is there a way to update outlook address book using excel? Is there a macro available for it? Thank you for your answers Mike ...

Urgent! How to prevent Saving with VBA Visio?
Hi there, I am working on a small application in VBA Visio and i want to know i there's any way to prevent saving the document, as it exists in VB Word? As for example: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True End Sub Or something like that? with visio, the routine "BeforeDocumentSave" does not have the argumen "Cancel". Is there any one who can help me? Thanks a lot in advance! : -- avalon2 ----------------------------------------------------------------------- avalon27's Profile: http://www.officehelp.i...

my file still exists but is blank please help
I had a virus completely delete a file in my microsoft word 2000. It's old and I don't know what to do. The file name is still there but the text is blank, and I've tried several recovery progrms that haven't retrived it. Was hoping someone knew if word has its own back to retrieve things from and how to use it If the contents have been overwritten (and it certainly seems as if that is the case), there isn't anything you can do, I'm afraid. -- Stefan Blom Microsoft Word MVP "hanna" <hanna@discussions.microsoft.com> wrote in messa...

Compress an Excel File
I have an excel file that is 36 meg. Do you know how to compress the file? When I zip the file it drops to 3meg so I know it can be compressed. I beleive the file has become large due to the constent changes I need to make to the file. Help Please. Hi Mandy Maybe this will help? http://www.contextures.com/xlfaqApp.html#Unused You can use Rob Bovey's Code cleaner to clean up your code http://www.appspro.com/utilities/utilities.asp -- Regards Ron de Bruin http://www.rondebruin.nl "Mandy" <mangelo@arbys.com> wrote in message news:42c401c47fa1$ddc405d0$a401280a@...

Tracking an email when sent directly from Excel
I often use the feature File>Send To>Mail Reciepient (as attachment) when I'm working in an Excel Workbook. However I can never find any record of this in Outlook, or anywhere else. Is there a way so this gets recorded in Outlook, or can you tell me where I can find some record of the email and its contents. Thank you, On May 18, 7:23=A0pm, Rob <robfl...@sbcglobal.net> wrote: > I often use the feature File>Send To>Mail Reciepient (as attachment) > when I'm working in an Excel Workbook. =A0However I can never find any > record of this in Outloo...

query help 01-08-08
My main table is called tblMain and this has an abundance of information in there. Each record has an ID called MainID. I need to do a 'search type query' on 4 fields . I have a primary business unit which each record must fill in (PrimaryBU). I then have another 3 related business unit fields (RelatedBU1, RelatedBU2, RelatedBU3). All 4 of these fields are linked to a lookup table tblBusinessUnit. I need to write a query that will prompt the user to type a business unit (I know how to do this part) and will then show any record which has that business unit in any of those 4 fie...

Help with econnect trying to create a contract for GP
Hi all, I am trying to use the serializer and create a contract/project and budget line items, i was wondering if anyone has some examples. When i use the serializer with contract, it drops off the pstat value in the xml document and then recive the error that a variable is empty. Thanks all ...

Diable excel macro for DATES
Hi, everytime i copy and paste from a website in this format XX/YY excel sees it as a DATE. how can i disable this feature..I just want t paste it as its raw form of XX/YY. When i cut and paste it in my exce and try to change the format by using TExt. it comes up with rubbis numbers. Thank -- Message posted from http://www.ExcelForum.com try formatting, as text, before you import -- Don Guillett SalesAid Software donaldb@281.com "evo >" <<evo.18vufd@excelforum-nospam.com> wrote in message news:evo.18vufd@excelforum-nospam.com... > Hi, > > everytime i copy ...

Reading Excel Checkboxes in Access
Hi All I created a spreadsheet with checkboxes (controls) on it for reporting purposes. I now need to import the data contained on the spreadsheets into Access. The data is not laid out in neat columns or rows, it was formated to be a stand alone report originally. My problem is not in reading cells within the spreadsheet, I'm able to do that. My issue is that I can't tell if a checkbox is checked or not. What I've done: If I execute the following code in Excel I return a boolean partAM = CheckBox15.Value I return true or false Here is my access code: Set xlapp = New Excel....