What is good practice and good code hygiene? Putting code in Modules or Sheets? I have this Excel Workbook, with user interfaces in each sheet. Each sheet within the workbook does a different part of some overall task. Should I place the code relevant to each sheet inside the Sheet objects, or in Modules? Group into one module, or separ...
Hi! I have and excel file which is connected to an external xml file and uses it as data source (every time I use "refresh all" it reads the xml file and updates the sheet with the data). I want to disconnect from the data source (so that the current data will be copied to the sheet, and pressing "refresh all" will do nothing). I trie...
Hello, bascially, i have got a column consist of data that i want those starts with "EMUA-I" to be place at the front, with the ascending order of date. Then i want the "non EMUA-I" part to be placed at the back with the ascending order of date. please take a look at this reference file : http://www.speedyshare.com/files/23397356/1....
Im having trouble takign my assigned variable and offseting it. What am I doing wrong? Public Sub SampleBox_Change() Dim str As Integer If (SampleBox.ListIndex > -1) Then str = SampleBox.List(SampleBox.ListIndex) End If End Sub Public Sub Samplesdel(str As Integer) Range(Range("BA1").EntireColumn, Range("...
Hello i want to make a form that have 2 or 3 text boxes that i can enter text values in it and vba search for this values if they are matched in 1 cell like if i enter "hello" in the first text box then i entered "world" in the other text box it search the hall collumn for a range that have "hello world" in it then copy paste it to anoth...
I'm teaching myself (read hacking) through some old Excel Macros that are quite long. However, as I change quite a few lines I want to be sure that I know when and where my new and re-hashed elements are occuring - or not. So my question/s is: How do I insert a simple message box function that will pop up when a loop/procedure/event ha...
The following VBA 6 in Excel 2000 code Resides in a form that has text boxes, comboboxes and buttons One of them is txtUsername, another is txtPassword --I inherited this code With shtName .Unprotect "thepassword" .range("somenamedrange").Value = cboComboBox.Value .txtUsername.Text = txtUsername.Text .txtPassword.Text = txtPassword.Te...
Hi I am trying to connect to oracle server from excel...i dont want to install oracle client in my system(presently there is no oracle client) So i am using Microsoft ODBC for Oracle Dim strConnection, conn, rs, strSQL strConnection = "Driver={Microsoft ODBC for Oracle};Server=ServerName;Uid=username;Pwd=password;" Set conn = Creat...
Hello, I have got a range of date in one column, which i want to shade the last 3 days in blue and the rest in yellow. e.g. If the date is from 1/7/10 to 10/7/10, i want to shade the (entire) rows with date 8/7/10,9/7/10 and 10/7/10 in blue and the rest 1/7/10-7/7/10 in yellow. (Date in column K) I need VBA script to perform the job...
I have the following vba: Data is all text, Rows A - J with column headers on every sheet are the same Data is sql queries all with "top 1000" 4 sheets (sheet1, sheet2, sheet3, Master) sheet 1: 100 rows sheet 2: 34 rows sheet 3: 900 rows Master: merged data from 3 sheets PROBLEM: Sheet3 only copies 84 rows specifically however addin...
The Excel 2003 API has a GetSaveAsFilename method that pops up the Save As dialog. This takes in various parameters, including the file type (xls, txt, csv,...), something like the following: GetSaveAsFilename(Missing.Value, "Microsoft Office Excel Workbook (*.xls), *.xls", 1, Missi...
I've tried this but it doesn't work. it skips the rows that have A[i] empty. Any help is appreciated. Sub Consolidate() ''# Local Variables Dim cell, cell2 As Range Dim wks As Worksheet ''# Step 1: Clear master ''# Step 2 : Loop through the regional sheets Sheets("Master").Range("A2:Z65536").ClearContents For Each wks I...
Is there any way to do a vba for each loop that loops a range but includes the empty cells/rows. PROBLEM: When I do a copy on the row from some other worksheet, the loop automatically skips the cells in the "A" column that are blank and skips that entire row completely and therefore I lose some data in that row because ONLY the A colum...
I am trying to set the data validation for a range of cells using VBA. I get a run-time error 1004 (so helpful) "Application defined or object defined error" with this code. With rngRangeToCheck.Cells(lrownum, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Form...
I have a spreadsheet that one department will scan a sample into and it will time and date stamp in the column next to it when it was scanned (sent). Then the next department will scan the sample into another column and it will time and date stamp when it was scanned (received). the code i am using is below, but because of the protection...
I have a macro which updates a cluster column chart when values are entered in the range start and range end cells. I would like to edit this VBA code to instead create a 100% stacked column chart. How do I do this?? See example of current code below: Sheets("Inputs1").Select 'ActiveSheet.Inputs1.Select Range("A4").Select RangeStart ...
Hi, I have an Excel 2007 Worksheet with many buttons and labels that act as menu options (i.e. user clicks the buttons, labels with images) and is presented with forms, or some thing else. These images / icons for the buttons and labels are loaded in VBA by assigning the Picture property of the Control and calling LoadPicture() method...
I have installed Oracle 10g Express Edition. When try to test the connection I am getting the error "Error while trying to retrieve text for error ORA-01019". Below is my code. strConnection = "Driver={Microsoft ODBC for Oracle};Server=Servername;Uid=username;Pwd=password;" Set conn = CreateObject("ADODB.Connection") conn....
Hello, I used conditonal formatting to select and color some data, and now i want to apply color sorting to rearrange the data. However, it seems like excel can no longer detect the color after i use conditional formatting. I tried several codes that i could find in google but none of them worked. Does that mean color sorting cannot b...
I have a list of numbers, each group of numbers is separated from the other group by the number zero "0" or many zeros. I would like excel to get the maximum number of each group and post it in a cell automatically. ...