Activate Workbook Code Only Works On Some PCs
Oct 20, 2007
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.)
View 7 Replies
ADVERTISEMENT
Jan 27, 2009
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..
View 2 Replies
View Related
Feb 19, 2009
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
View 2 Replies
View Related
Dec 11, 2008
I use a workbook where new worksheet tabs are added and removed daily. Every day I use the second-to-last tab (2nd from the right) and the last tab (far right). What code would enable me to activate and reference both of these tabs individually? I believe it would be something like the code below but I can't figure it out...
View 5 Replies
View Related
Mar 19, 2007
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
myListbox.Selected(myIndex) = True
View 4 Replies
View Related
Apr 23, 2008
I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3
Currently I have Wk3 showing in the screen.
Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?
This is part of a longer macro, so I only need to know how to do above.
View 15 Replies
View Related
Sep 16, 2009
I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls
View 4 Replies
View Related
Apr 22, 2007
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.
View 9 Replies
View Related
Oct 5, 2009
This snippet of code works fine when I run it. When another user uses this same workbook and runs it it gives an error:
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
This is the line highlighted by excel:
ActiveSheet.Paste
I have tried changing it to:
Sheets("Data").Paste
View 9 Replies
View Related
Oct 8, 2011
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
[code]....
View 1 Replies
View Related
Feb 26, 2009
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
View 9 Replies
View Related
Oct 3, 2013
How do I use VBA to Activate Sheet1 of a workbook? I am using the following to go BACK a sheet, but really need to get to the first sheet in the workbook.
Code:
Sheets(Sheets.Count - 1).Select
If it's a new wb, then it's tab is called Sheet1.If it's an existing wb, and it only has 1 sheet, then I need that one (in case someone has added a sheet and deleted Sheet1)
View 3 Replies
View Related
Aug 2, 2009
I am having a "cosmetic" issue that I was curious if I can fix it.. I have a workbook that opens a network workbook, saves some data to it, and then closes the network workbook.
Everything is working fine, except I cant get the code to "reactivate" the initial workbook. After excel saves and closes the 2nd workbook I opened, my screen stays on my desktop, instead of refocusing on the initial workbook. I have to manually click on the initial workbook in my taskbar to bring it back into focus...
I thought the below code would bring the inital workbook back into "focus" on the users screen, but it's not working.
Windows("NEWRightFit.xls").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Windows("sxssubmit.xls").Activate
Confirmation.Show
End Sub
I just moved the Application.ScreenUpdating line as initially I had it at the very bottom, but that did not correct. The confirmation.show is just a dialog box that informs the user the changes were saved successfully. I would like the first workbook "sxssubmit.xls" to come back into focus automatically though, without the user having to manually select it from the taskbar.
What is my code missing?
View 9 Replies
View Related
Aug 15, 2008
I have 2 workbooks open
Book1 has a call to ChangeBook()
Book2 is empty
Function ChangeBook()
Application.Workbooks("Book2.xls").Activate
End Function
ChangeBook() is located in a .xla file loaded on Excel launch.
Calling the function from Book1 does not cause Book2 to activate.
Running the function via the F5 key in the IDE works fine.
I am modifying existing routines that have problems and being able to activate certain books at will would make the changes much easier.
View 9 Replies
View Related
Mar 13, 2013
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
[Code] ..........
View 7 Replies
View Related
Sep 1, 2006
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
View 5 Replies
View Related
Apr 18, 2009
-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.
I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.
Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.
What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.
I have tried "Application.Activate" and can not make it work.
View 14 Replies
View Related
Jul 22, 2009
I have .xls files (ex: a.xls, b.xls etc) in a shared drive.How do I get information (run macros without changing anything) from a.xls and create a new file in my hard drive without opening a.xls. I don’t know if that is possible to do. May be sounds very weird. For example, I name this file on my hard drive as a01.xls, b01.xls from b.xls. Now I run other macros from a01.xls. which is active. I want this macros to run independent of the file name the user creates. In otherwords the workbook has to be variable. Not sure how to use ‘Thisworkbook’ function if that is what is needed to do.
View 12 Replies
View Related
Jan 26, 2010
On a command button in a userform, I have this code to open another workbook:
View 2 Replies
View Related
Jul 9, 2012
i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1
in Cell A1 is "masterworksheet.xls"
View 4 Replies
View Related
Nov 5, 2008
I have a workbook containing macros that opens a second one (no macros) and does some 'data mining'.
The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):
Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name
The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...
If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code.
tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming
View 9 Replies
View Related
Mar 19, 2008
button on main workbook opens 2 other workbooks and assigns a workbook object to them. the 2 opened workbooks are Activated in turn, range values changed and macros on these sheets invoked and results captured and pasted back onto the starter workbook. the macro is within a sub in a module as are the ones in the second workbook. An example of the code used is:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Dim wkbTest As Workbook
Set wkbTest = Workbooks("Test.xls")
wkbTest.Activate
Sheets("G").Activate
Range("Today").Value = Format(Now(), "dd-mmm-yy")
Application.Run "'" & wkbTest.Name & "'" & "!TestMacro" ............
View 5 Replies
View Related
Jan 21, 2014
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.
View 5 Replies
View Related
Apr 30, 2008
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")............................
View 2 Replies
View Related
Oct 28, 2012
I am writing a code that has a sheet change event linked to call on a number of macros. The vba works fine until I change another cell and it activates the same macro.
VB:
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range
Select Case Range(BI47)
Case "1"
[Code]....
I have tried if statements and everything else I can find, most will not work, others report errors. The code and macros will be the same on many sheets but work independently and BI27 is the source of the change
View 7 Replies
View Related
Nov 17, 2008
I have this code below, which someone from this forum kindly kindly gave to me for my Worksheet code. What it does is check the value in a dropdown list in column I and if it says "Cancelled" then strikethrough columns A-K of that row.
What I wanted to know is this, in cell N2 has the formula to show todays date. In column D9 onwards is an end date. How can I amend the code below so that it also checks to see if the date in Column D9 onwards is greater than N2 and if so strikethrough columns A-K in that row?
Would it be in the form of an OR statement in the code below?
View 9 Replies
View Related
Jul 6, 2009
VBA Code To Activate/Press Button On Web Page. press a button on web page using VBA
View 4 Replies
View Related
Aug 18, 2006
I have a button on a sheet which needs to remain protected.
However, the button code does not activate when the sheet is protected...
Any hints on how to "unprotect" just the button?
View 9 Replies
View Related
Oct 8, 2009
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.
View 12 Replies
View Related
Mar 19, 2012
I am writing code in Excel VBA and trying to control power-point. at the end there is message-box pop up. Everything runs fine.
But at the end I have go bottom windows bar and click on the excel file then message box comes up, otherwise excel tag keeps blinking at the bottom. I want to add something to code so message box in excel file is pop-up automatically without me clicking on excel file.
View 1 Replies
View Related
