This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.
I was tired when I wrote the code to activate the workbook and it is written:
Workbooks("Update").Activate
The updates have already been sent out and it is not working on some computers. (If I change the code to
Workbooks("Update.xls").Activate
it works fine.)
Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?
(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)
I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.
Const storeStartCell = "B5000" Private Sub ListBox_CounterTOs_Click() selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3)) selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4)) ListBox_TO_Name.Value = selectedTOName ListBox_TO_Number.Value = selectedTONumber End Sub
The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
The following code was working for me and now no longer works. Also, it works when I step through but not when I run it normally (it stops at the line imeediately following this line:
OrigWB.Sheets("PP&E").Move after:=DestWB.Sheets(DestWB.Sheets.Count) ) regardless of what code it is - even if it is only a message box.
The error message is code execution has been interupted.
****************** Sub PPEMerge() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.StatusBar = "Merging..." 'retrieve source docs: 'PP&E sheets from sec packages 'system beginning and ending balances
I have a workbook setup with 4 sheets that I'm using. I need the 4 sheets to type in all caps when I enter the data. I found a script, and followed the directions. It works great, yet when I close out of the workbook, and open it back up, the script doesn't work anymore. I go to check the 'view code', and it still there. I resave it, and try it again, and it still doesn't work, what am I missing? This is all new to me. I am saving it as a Workbook Macro enabled file type.
Once I get this resolved, is there a way to make it default to use the script everytime I open the workbook?
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub On Error Resume Next If Not Intersect(Target, Range("B3:O210")) Is Nothing Then Application.EnableEvents = False Target = UCase(Target) Application.EnableEvents = True End If On Error GoTo 0 End Sub
I put together about 10 separate macros that will log you into a site using the values in a given cell. I was having trouble with the last one because of AutoComplete remembering the username. So I put in an IF statement, and when I used F8 to go line for line, it works perfectly. However, when I click F5 and just let it play through, it doesn't log in. I tried adding a 5 second delay, but that didn't seem to work. I still get a run-time error when I hit play.
It get hung on this line:
Code: If doc.getElementById("user_name").Value = cUsername Then
But the complete code is this:
Code: Sub StreetLinks_Login() 'On Error Resume Next
Dim cURL As String cURL = Worksheets("Sheet1").Range("B9").Value
I am trying to write a function that searches a column for an index then sums the value in a corresponding column. This function works when run in the immediate window of VBA but when I use it in the spreadsheet it can't find any rows.
The reason I need this formula is because there are more than one rows that can be found.
Function SumIndex2(ByVal sField As String, ByVal sIndex As String) As Double
Dim i As Integer Dim dSum As Double Dim rng As Range Dim firstaddress As String
I wrote the following basic code earlier (which will end up being part of a larger code)
Code: Dim DateInput As String Dim LastCell As String
Sub LastRowInputBox()
LastCell = InputBox("What is the last row number in the range?", "Last Row Input")
[Code] ......
As you can see, all the ColumnCopyInsert Subroutine does is copy select/copy four columns (E:H), inserts the copied selection immediately to the right, inserts formulas and autofills down to the last row required. I added the LastRowInputBox routine as the range of rows varies from week to week (inserting new rows, deleting others).
The code works great...up to a point unfortunately. If ran as it is, everything that should be copied/pasted will be and cells autofilled to the row specified via the InputBox. Here comes the problem, if any rows are either inserted or deleted then the code throws out "The object invoked has disconnected from its clients" when it reaches the first instance of the following line:
Code: Selection.Insert Shift:=xlToRight
At which point, Excel hangs (or maybe in some sort of loop) and I have to open Device Manager and close the Excel exe process.
I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:
Sub FormattingAcutalReport() Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls" Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls" Dim myBorders() As Variant, item As Variant Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
I have this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
Sub RemoveCodeAndSave() 'Remove all code from ThisWorkbook code module ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
[Code]....
I get the error "Run-time error 440: The specified dimension is not valid for the current chart type" why I am getting this. The macro does everything I want it to, except for throwing the error at the end.
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code: Sub SaveSheetAsNewBook() Dim wb As Workbook Dim InitFileName As String Dim fileSaveName As String Dim wshape As Shape InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
I have a workbook with four worksheets named FilteredData FilteredDataFormat array arrayFormat
The two sheets named FilteredDataFormat and arrayFormat exist strictly to preserve headers , and column widths.
So that for convenience sake I can clear my data without specifying a range that excludes the headers. I simple clear the contents of the 'FilteredData' and 'array' then copy and paste 'arrayFormat' and 'FilteredDataFormat'.
Sort of an unconventional and lazy approach but I see no reason why this will not work.
Here is my original code and what happens is that excel creates another workbook for some reason unknown to me
I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.
How would I go about doing that? I have this code so far to open the workbooks that are in the folder.
I need to error check to see if a worbook is open or not. if it is not open i need it to open it. i supplied the code i am trying but it is not working. I keep all my workbooks in XLSTART.
I have two workbooks the first named "number" which just contains the number 50000 on sheet 1 cell A1. This "number" workbook is always closed and saved in c:me"number". The second named "sales" has a form control button with code as follows and is always opened and saved in c:me
Sub next invoice() range ("A1"). Value= range ("A1"). value+1 End sub
When I press the button this code does not work because I am missing the vba code to find the close file.
I need a code in VBA which I have to use the Hyperlink within the workbook e.g. "Place in This Document.
Assuming I have two Worksheets WS1 = Main, and WS2 = Reference respectively
In WS1=Main, I have a list in Column from E3 to E1000. let say E3=1, E4=2, E5=3, E6=4, E7=5..., so on and so forth.
In WS2=Reference, I have a list in Column from F5 to F1002. let say F5=1, F6=2, F7=3, F8=4, F9=5..., so on and so forth.
What I want is if I click the E3=1, E4=2, E5=3, E6=4, E7=5..., in WS1=Main if will jump to specific cell in WS2=Reference e.g. F5=1, F6=2, F7=3, F8=4, F9=5...,
Does anybody know the memory limitations that Excel (2003) has with regard to the amount of vba code that a workbook can contain?
I have automated one of my spreadsheets to load a bunch of modules containing code (a few hundred thousand lines of code altogether). Before it finishes loading all of the modules I get a "Run-time error '7': Out of memory" error. When I press the Help button on the dialog I get a help page which says:
"More memory was required than is available, or a 64K segment boundary was encountered."
The help page goes on to suggest (among a few other things) that I might have a module or procedure that is too large. After reducing the sizes of individual procedures and modules, I am starting to think that there may be a limit to the absolute number of lines of code that can be stored within a single workbook.
I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1
The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3
Would it be possible to modify this code so it saves the entire workbook?
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.DisplayAlerts = False Dim bk1 As Workbook Dim bk As Workbook Dim myfilename As String myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls" Set bk = ActiveWorkbook Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet) bk1.Worksheets(1).Name = "Sheet1" bk.Worksheets("Sheet1").Copy _ bk1.Worksheets("Sheet1") On Error GoTo 0 bk1.SaveAs Filename:=myfilename, _ FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWindow.Close
