Activating Workbook

Apr 24, 2008

I want to copy from a Workbook into an Excel Worksheet and close the Workbook.

Workbooks.Open (stgPosition)

'Windows("Symbol Assembler.xls").Activate

Workbooks (stgPosition).Activate
Application.CutCopyMode = False

When the Debug gets to "Workbooks (stgPosition).Activate", I get an error message:
Routine error "9" Subscript out of range.

View 9 Replies


Activating Certain Workbook

Sep 27, 2006

I have multiple workbooks open, and would like to activate a workbook whose name has a certain string in it. How would I do this with VBA?

View 3 Replies View Related

Activating An Open Workbook

Jul 17, 2009

This has been kicked around for some time and so far there isn't an answer.

I want to approach it a little different this time.

My users can have let's say 5 separate instances of Excel open.

The user clicks on a macro to open a workbook and I have programmed a message that says "This File Is Alredy Open".

The code I used is: ....

View 6 Replies View Related

Activating New Workbook That Hasn't Been Saved As Yet

Apr 3, 2014

I am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?

Here is name code:

[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")

Options = InputBox(Prompt:="Scheme Code", Title:="Options")
Options1 = InputBox(Prompt:="Scheme Code", Title:="Options")
Options2 = InputBox(Prompt:="Scheme Code", Title:="Options")


View 7 Replies View Related

Activating Another Open Workbook With Unknown Name

May 15, 2009

I have my VBA Codes set in a workbook (Production) that my supervisors open to calculate production. So when they open the workbook (Production) I have a button that they push that starts the code however I need to put something in that activates the other open workbook, The problem is that the name of the workbook that they run the code on can change.

View 2 Replies View Related

Activating A Workbook That Was Previously Active

Dec 21, 2009

An Excel file is e-mailed to my company for all the credit card transactions in a given day. Because we manage many stores and each store is a different legal entity and a different fiscal year ending, I need my macro to sort the information and separate the transaction by store and by fiscal year end. I just hit my first block.

I need to open a workbook off the server to grab information for a vlookup formula. I can get the workbook open, but I can't get back to the original workbook or find a way to reactivate it. Because the macro is going to be saved in the "personal" workbook, I can't use the "thisworkbook.activate" code. Also the workbook name and tab name that is emailed to me will always be different so I can't use other solutions that I've seen posted.

View 2 Replies View Related

Object Error - Activating Worksheet In Workbook?

Aug 1, 2014

For some reason I'm consistently getting an error in the following line:

Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer


View 6 Replies View Related

Activating Add Ins

Jun 8, 2006

Below is code I use to try to 1. call a sub that take the users to a specified location on a specified shhet, 2. one to remember user menu set up, 3. one to remove tool bars, 4. one to set up menus for operation of my program and code to activate two addins required by my spreadsheet program.

If neither of the analysis addins are selected prior to opening the spreadsheet, when the spreadsheet is opened the appears to work properly except that the 'Start' sub is not initiated. If the spreadsheet is saved, closed and reopened it all works perfectly.

However if both of the analysis addins are selected prior to opening the spreadsheet for the first time all code appears rto work perfectly.

Private Sub Workbook_Open()
Application. ScreenUpdating = False
Call Opening_Screen
Call Toolbars_Record_List ' writes to sheet cbars the names of command bars to recall on leaving
Call Toolbars_Remove
Call Endusermode
Application.AddIns("Analysis ToolPak").Installed = True
Application.AddIns("Analysis ToolPak - VBA").Installed = True
Call Start
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Last Row Or Column Without Activating A Sheet

Dec 26, 2008

I have the following function: ..

View 7 Replies View Related

VBA Message When Activating Addin

Nov 7, 2008

I have an addin "My Menu" that runs several routines. These routine require the sheet to be converted to text "Sub text ()" no problem so far. But I want to warn the user that the sheet is about to be converted to text.

Is there a VBA way of displaying a message box when "My Menu" is clicked on that says "Sheet will be converted to Text OK ?"

View 9 Replies View Related

Activating A Dynamic Hyperlink In IE

Jul 19, 2006

I am trying to activate a Dynamic Hyperlink in IE. Each time a user starts a new session, a single parameter in the URL changes. Is there a way to search the page, identify the preferred link, and activate it without knowing the full hyperlink address?
The hyperlink is as follows: [url]
The parameter after "unitchange" is what changes from session to session.

View 9 Replies View Related

Function Activating Without Known Trigger

Aug 2, 2006

I am encountering a strange situation with my Excel 2000. I have a public function, in a module in the VBA project associated with my workbook. But I'm not calling it from nowhere inside the code, or from other macros - it is not being referred anywhere in the workbook. Yet, after I make a slight change in code and not save my changes, when I return to the workbook and select a value from any cell with a validation-list (regardless of the sheet where it resides), that particular function is being executed!

View 9 Replies View Related

Activating Sheet From A Dialog Box

Feb 2, 2007

I have made a dialog box open using VBA to select some file,

Function FSel()
filetoopen = Application. GetOpenFilename("Document Files (*.xls), *.xls", 1)
Workbooks.Open Filename:=filetoopen
End Function

I need to activate the sheet which is selected in that string "filetoopen".I am not getting the way to do this ,the normal Windows(" " ).Activate is not working with variable as input.

I am placing the funcation Fsel in a loop so each time it asks for the file to open which is not proper,so i wanted to activate sheet .

View 9 Replies View Related

Error 9 When Activating A Worksheet

Mar 23, 2007

All code is called from another workbook

This fails on the second line with a type 9 error, ie worksheet not found

Worksheets("Rent change details").Activate

This works:

Workbooks("RENT_EXPLANATION.xls").Worksheets("Rent change details").Activate

Surely both should work since the default qualifier for the worksheets object is the activeworkbook? This only fails in Excel 2003, in the same app. in Excel 2000 it works.

View 4 Replies View Related

Selecting Vs Activating Then Copy

Mar 30, 2007

I know there is a simple answer for this, and I did a search but could not find what I am looking for. I am comparing two different workbooks. I loop through the first workbook (oldfile) and then find the corresponding value on the other file. I find the corresponding value using

With Worksheets(1). Range("b2:b" & blah)
Set c = .Find(MyVar, LookIn:=xlValues)

I know the cell address of the value that was found by

firstAddress = c.Address

Now I need to offset a few columns and copy the data. If the cell address was in R1C1 format then I could do this easliy by ActiveCell.Offset(Row, col + Offset).Copy

(where row and col are given from the c.address function. But I don't know the RC value since the c.address provides the value in "A1" format. I do not want to actually go to the cell and then do an offset and copy, since it is a large spreadseet and would be slow. I don't want to do a bunch of string manipulations (unless it easy). Is there a way to have c.address output it's values in R1C1 format? Or is there a different command I could use?

View 3 Replies View Related

Activating Worksheets In Different Workbooks

Apr 28, 2007

I have created a macro which unhides a sheet (Email) in one book and takes that info to #1 create an email and #2 open a new file and paste data into it.

Problem is, I want to go back to the first book and hide the sheet (Email) since other users will be using the macro. I keep getting a run time error 9 'Subscript out of range'.

I have my code below. I have commented out the last few lines that used to work before I had to open a new book first.


View 9 Replies View Related

VBA Activating Code On Mouse Hover

Apr 19, 2013

Is there any way I can activate a sub routine when the user passes the mouse cursor over an activeX object.

For example this activates code when the user interacts with an activeX scroll bar:

Private Sub ScrollBar2_gotfocus()
Dim A, B, C
If OLEObjects("checkbox1").Object.Value = False Then
B = Range("B4").Value
A = (100 - B) / 2
C = (100 - B) / 2
End If

IS there a "gotfocus" equivalent such that code will run if the user simply passes their mouse cursor over the scroll bar EVEN IF they dont click it?

View 1 Replies View Related

Activating Multiple Array Formulas At Once...

May 14, 2009

Does anyone know how to activate a block of different array formulas at once??


N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.

In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.

btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.

View 14 Replies View Related

Page View Without Activating Sheet?

Mar 3, 2014

Is there a way to change the page view without activating the sheet? I would like to loop through a bunch of sheets and change the page view.

I currently am aware of the ActiveWindow.View command, but this requires me to first activate the sheet. Is there a similar Sheet.View command, or something like that?

View 3 Replies View Related

Copy Data Without Activating Worksheet

Feb 23, 2008

I have a macro that copies selected data from various cells in WS1 to WS2's next open row, using offset to step to each new open cell in that row.

View 14 Replies View Related

Activating A Toolbar Item From A Macro

Sep 24, 2009

From a macro, I am trying to activate the Excel dialgue that occurs when you click on a toolbar item.

Specifically I want the Find item (CTRL-F) to activate. I can use the .find method ok from a macro but I want the dialogue from Excel itself.

View 2 Replies View Related

Activating Sheet Whose Name Contained In Variable

Jan 20, 2012

I am attempting to use the code below to activate a sheet whose name is contained in a variable. The line in Bold gives me a "Subscript out of range" error even though the variables seam correct in the debugger.

Private Sub GetFromArchive_Click()
Dim wkb As Workbook
Dim strPath As String: strPath = "M:EfpDocsArchivesESI Processing Log Master Archive.xls"
Dim lngListItem As Long, lngSelected As Long
Dim varSheets() As String

[Code] .......

View 2 Replies View Related

Activating Userform With A Button Click

May 19, 2013

When my worksheet is activated, code is executed which defaults the values in my userform controls. The users enter the appropriate data, and with the clicking of a command button, the data is entered into the worksheet. When that is complete, the idea is to return the user to same form, in a default value so they can enter in new data for submission. This will continue until the user selects the Exit button onthe userform.

With wshairport
.Range("A" & llastrow).Value = uf2.ident
.Range("B" & llastrow).Value = uf2.freq
.Range("C" & llastrow).Value = slocation
.Range("D" & llastrow).Value = slat
.Range("E" & llastrow).Value = slong
End With
Application.EnableEvents = False

This is resulting in an 'Object doesn't support this property or method.'

I'm not sure about the appropriateness here of the 'Application.Enableevents=False" here. Since the fields of the userform have change events associated with them, I need to disable that what the userform activation code defaults them.

View 2 Replies View Related

Activating VBA Code When Entering A Cell?

May 9, 2014

I am trying to write a small bit of VBA code, so that I can record the row and column of the active cell. I have got it working when I CHANGE the value of the cell, but I need to get the code to fire off when I ENTER the cell.

The code section is below:-

Private Sub Worksheet_Change(ByVal target As Range)
'Store the row and column into G1 and G2 for the drop down loading
Call RowNum(target)
End Sub


how I can do this, as I cannot find reference to a Worksheet_Enter function ?

View 2 Replies View Related

Activating An Open MS Word Document **

Oct 1, 2007

I'm trying to write an Excel VBA macro that opens a .csv file, selects a range of cells, opens a new, blank Word document, and then pastes the cells into the Word document.

I can open .csv file.

I can open MS Word.

I can select and copy the cells in the .csv file.

I cannot figure out how to paste those cells into Word.

View 3 Replies View Related

Subscript Out Of Range: Activating Window

Aug 28, 2007

I'm using the below code to create a new excel file and activate it. But I'm getting the error message "Run-time Error'9': Subscript out of range" at Windows().activate command.

tdt = Format( Date, "dd-mm-yyyy")
nameoffile = "Recon Status" + tdt

ActiveWorkbook. SaveAs Filename:=nameoffile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

View 3 Replies View Related

Display Message Box Upon Activating Worksheet

Jan 22, 2008

I'm trying to add a message box that will appear when you activate a worksheet in a workbook. This is what I have, but it's not working.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh. Name = "P2 Forecast" Then
MsgBox "Ensure you have locked your forecast on the Sales Forecast Tab prior to working your P2s"
End If

End Sub

View 9 Replies View Related

Activating Chart Object In Protected Worksheet

Mar 5, 2013

I have a worksheet which is used to display analysis data to the user. The sheet will be further protected with only some cells available to the user. I've used the attached code to retrieve some data from the sheet, clear a bunch of cells to reset the sheet and reset a graph (the graph itself is plotted as a log-log with the 1 to 100 on the Y-axis, hence the resetting of data to 0.5 to push it below the axis and make it invisible ready for later data dumping).

The code worked fine until I protected the worksheet. I have set the charts to unlocked using the format option in the chart area prior to locking. When I run the code though, it stops on the line indicated with the error "Application-defined or object-defined error". I've double checked and Chart 7 is the correct chart, and it is unlocked according to its format properties.


'get date of survey and equipment number for retrieving the data from the archive
dateSurvey = Worksheets("Calculation Page").Range("B2").Value
equipNum = Worksheets("Calculation Page").Range("F2").Value
'stop screen updating
Application.ScreenUpdating = False


EDIT: Oops, I've just noticed that even if I unprotect the sheet, I get an error on the .select within the seriescollection stating "Method 'Select' of Object 'Series' Failed", and the code worked perfectly before. I'm completely lost now...

View 9 Replies View Related

Excel / VBA Popup From Activating Specific Sheet

Apr 12, 2013

I'm trying to create a popup message that appears when a given worksheet is activated. I can't figure out the syntax to get it right.

View 3 Replies View Related

Activating Change Event On Button Click?

May 11, 2012

I have this code that ideally should change font color in usedRange to red upon button click. The problem is it's not working and I've been pulling my hair out all day (not that there's much left of it)

HTML Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim UsedRange As Range
Dim cel As Range


and the related "End If" this works i.e. if I click on any cell within respective sheet font in used range all changes to Red but I only want this to change on CommandButton1 click which I have inserted in sheet.

View 7 Replies View Related

Copyrights 2005-15, All rights reserved