Answer Questions

• cggamer Efficent Way to manage multiple textboxes (maybe a loop?)

  This might be really easy, or maybe it's really hard I'm pretty new to VBA I'm using the VBA editor in Excel 2003 to automate some data entry into a spreadsheet. I have several userforms all with various numbers of textboxes, the largest having 34. What I am trying to do is use some sort of loop to clear the value out of each textbox when the user creates a new record. I want to do this mostly because I’m lazy, but also because I foresee the need to be able to expand this later. I’m trying to avoid the following situation: With userform1 .TextBox1.value = "" .TextBox2.value = "" .TextBox3.value = "" [etc…] End With I’ve ...Show All

• rajjon Completly Lost with Data Validation Code

  I am trying to write a small script which will allow for multiple choices to be selected from a dropdown list, but for ONLY the dropdown list to accept input (ie data protection). I had asked for help before, and the nice people at Contextures sent me to their site to download one of their spreadsheets (dv0017 http://www.contextures.com/excelfiles.html). I have been looking at the code, and I am clearly in over my head. The code is below. Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String 'I think this means that if there is more than one cell selected, then exit If Target.Count > 1 Then GoTo exitHandler On Error Resume Next 'Absolutely no clue here. I have read a few di ...Show All

• Howard555 Find and Replace Carriage Return in Excel with VBA

  Hi all, I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains "[]" that are carriage returns. so i need to find these characters and replace with null can i do this with vba many thanks Hi, you could try something like this: Dim objExcel As Excel.Application ' Excel object Dim wbkTemp As Excel.Workbook ' Workbook Dim wksTemp as Excel.Worksheet ' Worksheet ' Open record sets and objects Set objExcel = CreateObject("Excel.Application") Set wbkTemp = objExcel.Workbooks.Open(C:\Temp.xls) Set wksTemp = wbkTemp.Worksheets(strQuery) wksTemp.Activate wksTemp.Range("A2: ...Show All

• Alan Browne How to show dates in forms by the order of time?

  Hello, all, I have a little database which deals with patients paper work. I have two tables. One is patients' basic information like name, race, age, and so on. The other is the patient's paper work including the date when the patient finishes the paper work. So I have two forms. One if for the patients' basic information, the other form is a subform of it to show the paper work information of the patient. My question is, when I try to see one patient's all paper work, say, 2 records, they are shown by the order of number, but not time. e.g. The first record will be paper work on 10/24/2006, while the second record will be paper work on 12/6/2005. I hope the records can be shown by the order of time. I mean, when I check the s ...Show All

• riant78 Text file search, return data from line other than search string line

  Hi all, I am trying to pull information from a text (.txt) file and input it into an excel spreadsheet. With the "instr" and "mid" statements, I can pull any information from the line that the searched text is on, however, I want to pull information from the two or three text lines after the searched line. The reason I want the lines after the search string line is because the search string line is always the same, but the information on the lines afterwards are never the same. Here's an example of one series of lines in the text file: Case 1005 1 35 66 90 4 22 5 99 What I want to do is search the file for "Case" and then extract the two lines of data beneath it and place them on individual cells in ...Show All

• Keith Newton Help with comma separated values in report

  Hi! A bit of a stuck-up. It’s no problem in creating a coma separated fields in .txt kind of file. But I have another problem which is probably simple to manage (not for me though - my brains work 24hrs/day and don’t come up with nothing). I want to show several different values from one field (in query) on the report - now check this - They have to be separated by a comma in one line. If anybody is familiar with the solution just show me the direction and I’ll try to solve it by myself. Thanks in advance Hi Kdee, did u find the solution, me too have a same problem, can u post here the final code that solved ur problem. Thanx shah Try using Set i.e. Set okno = [Report_Faktura-lot-osnovna].CMR_ji ...Show All

• Kimbe [OTP] VBA Using combobox in Excel

  H i there,   I'm working on an engineering spreadsheet (Excel) and need some help in manipulating comboboxes / dropdown list.   Say i have 2 list, where list A has the list of countries and list B has the list of cities for the country chose. If all the data source (i.e. countries and cities) are to be extracted from the Excel spreadsheet, is it possible to make list B dependant on list A (that is to say when user selects a country in list A, list B will automatically gives the respective cities) WITHOUT creating a userform If so, please advise how.   Hope you can understand what i'm trying to ask here. Fyi i have minimum VB knowledge so a "dummyproof" explaination would be really ...Show All

• sachin kumar rana Variable declaration + assignment

  I want to declare a variable and assign a value to it in one line. I have Option Explicit turned on.  The following line will not compile, I get an error stating;  "Compile error:  Expected: end of statement" Here is the code causing the problem; Dim count As Integer = 1 However I found the following code caused no problem; Dim count As Integer count = 1 I also ran into trouble with an IF/THEN/ELSE statement. It seems VBA is very particular about where exactly the keywords must be placed in relation to newline characters. It seems that THEN must be on the same line as its corresponding IF. Furthermore when I tried to follow ELSE with its corresponding statement on the same line, VBA place ...Show All

• ngaisteve1 Error Handling Code

  I've written some error handling code in one of my routines. This is to trap an error that can occur with an equation, which under certain circumstances, can divide by 0. The result of this equation I'm assigning to a cell's value. The code is per below: Sub() Unrelated code preceeding the error handler code On Error GoTo ErrorHandler Equation here immediately below Other unrelated code Exit Sub ErrorHandler: Set cell value = 0 Resume Next End Sub It works fine and traps the error and continues with the operation gracefully. However, further on in the code, completely unrealated to the particular process which I set the error code up for, if there's another error of some ...Show All

• Nick K. Questions on Excel.VBA -> Range.End()

  Hello guys, I am facing some bottleneck about moving a Range object in a sheet. I am using RangeObj.Offset(0, 1) to move my RangeObj to right, but it is not the same as actually using the keyboard arrows to move a cell. And I am using RangeObj.End(xlToRight) to move my RangeObj to End-Right. The thing is that RangeObj.End(xlToRight) will skip all the hidden columns. I wish to stop in a hidden column when applicable. Anyway, I am trying to understand how everything works. Which function is used when I press arrows on keyboard to move the current selection How do they implement the functions to skip hidden cells How do I implement function differently to select hidden cells instead of skipping them I can do the slow way ...Show All

• newyuppie Filter string in GetOpenFilename method

  L/G, I wrote the macro below in order to be open particular type of files in Excel. In fact it is a text file, but it has always the same name, let's call it "AAAA". I would like to have a filter in order to be able to see only this file in folder when i use GetOpenFilename. But the filter string in GetOpenFilename method doesn't work. The code shows all the files in the current directory. What is the right filter string Any help will be appreciated. Here is the code Private Sub OpenAAAA() Dim strAaaaFullFileNm As String Application.StatusBar = "Opening AAAA" ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path strAaaaFullFileNm = Application.GetOpenFilename("AAAA file, AAAA", 1, &qu ...Show All

• mikstr Newbie problem: storing in an array

  Hello, I'm new to VBA. I've some questions regarding to array. Here's the code linked with excel spreadsheet: Function Function1(DailyClose, EFBillsYield) TradingDays = Application.WorksheetFunction.Count(DailyClose) For i_cnt = 1 To TradingDays - 1 DailyReturn = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1)) Next i_cnt AnnualReturn = Application.WorksheetFunction.Average(DailyReturn) * TradingDays AnnualVolatility = Application.WorksheetFunction.StDev(DailyReturn) * Sqr(TradingDays) RiskFreeRate = Application.WorksheetFunction.Ln(1 + EFBillsYield) Function1 = (AnnualReturn - RiskFreeRate) / AnnualVolatility End Function When I enter the parameters DailyClose (which is an array of n ...Show All

• Aaron Schnieder Can't open regedit

  Hai.... Why if I want open regedit (run>regedit), and then show message error like that The application failed to initialize properly (0xc0000005) Please anybody help me... Jebat Thanks XP Profesional (5.1, Build 2600) No, I wouldn't delete anything. There must be one of your programs that still needs the regedit. If you delete it, then it won't run. I would advise you to just leave it. Your SP2 disk should automatically be taking care of things. Maybe you have loaded a program requiring the NT, but still is not all that compatible with windows. It could now be obsolete. Something somewhere in there is holding on to NT. You ca ...Show All

• dakota367 Excel: Email Macro help

  Hey All, I have the following macro code Sub Button1_Click() Dim EMail As Object Dim Link As String Link = " <web/file link " 'Filepath Set EMail = CreateObject("Outlook.Application") 'Assign Outlook application to variable With EMail.CreateItem(olMailItem) .To = " <email address> " 'email address .subject = "Update" 'Something for the subject Field .Body = Link .Send End With Set EMail = Nothing ' release memory End Sub As you can see from the code, you click the button and will send the link via email. In the code above what do i need to remove/add to stop the email from sending automatically and actually open instead of sending straight away Please ...Show All

• Xavier Arnau Passing new parameters to Report(.rdlc)

  Hi guys, ive sucessfully made a simple report(.rdlc) in VB.Net using the "wizard" of Visual Team Suite. Now, is there anyone here who could help me pass a new parameter to the report For example, i have a report that shows ALL the transaction of a company. Now, what need I need to know is how will I pass a new parameter to the report if the user only wants to view certain data like data from a Jan. 1, 2006 to Jan. 5, 2006 ...Show All