Workbook_open Runs On Closing
Feb 16, 2007
I've a problem with the below code.
Option Explicit
Private Sub Workbook_Open()
Dim x As Date
x = InputBox("Enter End Date!")
Range("B2") = x
With Application. CommandBars("File")
.Controls("Save").Enabled = False
.Controls("Save").Visible = False
.Controls("Save As...").Enabled = False
.Controls("Save As...").Visible = False
End With
With Application.CommandBars("Standard")
.Controls("Save").Enabled = False .................
View 9 Replies
ADVERTISEMENT
Sep 11, 2009
In the various codes I am using AutoOpen, AutoClose, Workbook_BeforeClose and Workbook_BeforeSave Subs. BeforeClose and BeforeSave are in '_ThisWorkbook'; AutoOpen and AutoClose are in a Module. I also use Workbook_Open, Workbook_Activate and Workbook_Deactivate, also in _ThisWorkbook. I put message boxes thru out code to be sure code was running right. The Workbook_BeforeClose is running twice under certain condition (when a cell in active workbook sheet is blank ("")). I do not understand why it is running twice.
For condition where there is data in cell M10, this creates a whole other issue ( multiple running of mutliple various Subs) which I believe I should deal with in different thread/question.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("PanelSched").Activate
MsgBox "Test-Before close"
If Range("M10") <> "" Then
Dim PanelName As String
PanelName = Range("M10").Text
View 9 Replies
View Related
Sep 14, 2006
I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?
View 3 Replies
View Related
Jun 4, 2008
I want to use this command when I open every workbook.
How do I do that? I know you're supposed to put the macro in the thisworkbook module in the workbook, but I have 600 workbooks that needs to be changed. That is just not a good idea. I've tried to put the macro in the workbook called own.xls (I use a swedish version of excel, dont know what it is called in english) thisworkbook,
View 9 Replies
View Related
Nov 18, 2009
I script that on open would pop up asking if a sync was done with an option for yes or no. If yes is pressed then it would just open, if no selected it would give a message "must sync before use" and close the workbook.
I do not want this message to pop up when other spreadsheets are open when this one is still open so im guessing private workbook_open
View 9 Replies
View Related
Jun 26, 2007
I am using the following code in my Workbook_Open sub, located in the ThisWorkbook code module. I am enabling macros when opening. All other VBA code in the project is running correctly.
Private Sub Workbook_Open()
Worksheets("Sheet1").init
MsgBox "starting"
End Sub
The init sub did not appear to be running so I inserted the MsgBox to confirm, but that doesn't come up either.
View 6 Replies
View Related
Oct 14, 2008
I have an Excel add-in file (stored .xla format) that is used by a lot of other spreadsheets on a network location because it is modified often, and all workbooks made from a template reference it. everything with that works fine, and any changes made to the add-in file are always reflected upon startup in the workbooks. So there isn't a problem with that. Recently I've had to add a Workbook_Open() sub into this add-in (which I wrote in 'ThisWorkbook'). However, this macro is never entered by any of the workbooks. Is it possible to have a workbook_open sub in another file? If so, what can be causing it to fail (there aren't any errors or anything like that in the sub)?
View 4 Replies
View Related
Dec 29, 2009
I have noticed this on more than one workbook with an Workbook_Open macro. When you open Excel, open Workbook1, do some work , save or don't save and close, then reOpen Workbook1 without having closed the Excel application, the Workbook_Open macro in Workbook1 doesn't run.
Closing Excel and reopening Workbook1 initiates the Workbook_Open macro. It's as if Excel remembers having previously opened Workbook1 and so it doesn't rerun the Workbook_Open macro the next time you open it.
View 3 Replies
View Related
Dec 9, 2008
I have 3 userforms:
COS
COSP
SLevel
and I have the code
Private Sub Workbook_Open()
COS.Show
End Sub
View 9 Replies
View Related
Jun 14, 2007
from 1 sheet i am opening several other worksheets and read in the bits i want.
i am finding that on occasions the workbook_open on these sheets is causing great performance issues because of the calc mode set to automatic in the open routine of the individual sheets. i dont need it to be set to auto but the sheet owners perfer it to be set like this and i cant turn it off their sheets. is ther a way of opening a workbook from a macro and then override its workbook_open routine?
Set wb = Workbooks.Open(sSourceFolder & "" & "mySheet.xls", False, True)
i cant find any other switches that i could use to disable this.
View 2 Replies
View Related
Feb 25, 2008
why the code below might not execute ? It just seems like it skips over the code and doesn't run at all.
Private Sub Workbook_Open()
'AssignRequestNumber()
MsgBox ("Whoa")
Run "AssignTrackNumber"
End Sub
I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.
View 9 Replies
View Related
Apr 20, 2006
i have a workbook iam using as a template.(workbook "template") the user opens which then requests a job number which will then saves the workbook as the enterd job number. this workbook also contains all the material data. eg: price and type which is on sheet "data". I am hopin to get to the sheet "data" from another workbooks command button by bypassing the job enter request by the workbook_open sub. basically i need some code that disables a workbook_open sub
View 8 Replies
View Related
Sep 22, 2006
Private Sub Workbook_Open()
If ActiveWorkbook. Name = "UserA.xls" Then
Call PreviewList
Else
Cancel = True
End If
End Sub
After the macro auto-startup, UserA works on the worksheet and input some procedures and save as ClientA.xls. However, before save as ClientA.xls and close, UserA wants to change the worksheet name and Procedure name as below, how to do that?
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "ClientA.xls" Then
Call RevisedList
Else
Cancel = True
End If
End Sub
View 2 Replies
View Related
Jan 20, 2007
problems with Global Static Variables.
In the Workook_open Sub I want to define 2 global static Variables.
For some reason I can't get it to work.
Whenever I try to access these vars they have the value "0"
I use Excel 2000.
Here is what i did:
Dim i As Integer
Dim ws As Worksheet
Public Static GlobalStartX As Integer
Public Static GlobalStartY As Integer
Private Sub Workbook_Open()
GlobalStartX = 6
GlobalStartY = 21
End Sub
View 4 Replies
View Related
Dec 6, 2007
the following workbook open event which repeats a macro called "Refresh" every 30 minutes. However, there is another macro "tame_blph" that i wanna call at 12 midnight everyday.
Private Sub Workbook_Open()
Call tame_blph
NextTick = Now + TimeValue("00:30:00")
Application .OnTime NextTick, "Refresh", , True
End Sub
Is it even possible to have multiple Workbook open events and/or multiple NextTick or something?
View 3 Replies
View Related
Aug 1, 2009
I have built a complex vba & multisheet spreadsheet that I am looking to secure against all the common attacks. So I have:A Workbook Open pw;
VBA password (29 symbols/numbers/Caps/lower case)
Very Hidden worksheets
Hidden rows/columns
Restricted scroll areas
Workbook protection
Code that auto protects all sheets upon opening
Registry referencing in Workbook Open with timed closure if not matched
Now I am on the last leg of implementing protection against Application.EnableEvents = False; force enabling of Macros and hiding of toolbars, scrollbars etc... Phew.
Soooo, in my research, I have learnt that if EnableEvents = False, Workbook_Open is essentially skipped and the security VBA routines are disabled. To get around, I have copied the entire contents of the Workbook_Open routine to a module under Auto_Open. The first line of both these scripts is: Application.EnableEvents = True.
Works! So far so good.
However, I have a Msgbox prompt in the scripts that displays twice. So in essence, Excel is running Workbook Open first, then Auto Open second.
If I open another instance of Excel and run Application.EnableEvents = False first before opening my spreadsheet, I only get one message. So only the Auto Open script runs.
View 9 Replies
View Related
Apr 19, 2006
i have this code which askes the user for a job number once the workbook is opened.
Private Sub Workbook_open()
' If sheet was named by original open routine, exit
If ActiveSheet.Name = "Main Roof" Then Exit Sub
' otherwise
Do
Returnvalue = InputBox("Please Enter a New Job Number.", "Information")
' Allow changes by entering q as the Job Number
If Returnvalue = "q" Then Exit Sub
' Delete the ' from the front of the following two lines and
' then when you enter q as the Job Number you will also be
' asked for a password. The default password is toe.
what iam trying to do is get it to open the userform "WorkSelection" after it has completed the above code.
View 3 Replies
View Related
Apr 21, 2006
i have been trying to hide the userform from the workbook open event with no luck
Private Sub Workbook_Open()
ufmTheEstimator.Show
Dim Worksheet As Excel.Worksheet
If Me.Worksheets("Main Roof"). Name = True Then
ufmTheEstimator.Hide
End If
End Sub
View 4 Replies
View Related
Jul 3, 2007
I've been setting the above property to TRUE after making some changes in the Workbook_open event in order to stop annoying messages if you haven't physically made changes.
Unfortunately, it doesn't seem to copy through to the workbook_beforeclose event and I'm being asked if I want to save changes even though the macro made the changes and then set the property.
Here's my code for the open event :-
Private Sub Workbook_Open()
Application.ScreenUpdating = False
On Error Resume Next
For counter = Worksheets.Count To 2 Step -1
Sheets(counter).Visible = xlVeryHidden
Next
Sheets("Desktop").Visible = True
Sheets(1).Visible = xlVeryHidden.....
View 9 Replies
View Related
Dec 31, 2013
I wrote this VBA for a particular sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H5") = "Y" Then
Columns("L").EntireColumn.Hidden = True
Else
Columns("L").EntireColumn.Hidden = False
End If
End Sub
If the value of H5 is "Y" it hides column "L". It was working fine at one time. Then I wrote a bunch of UDF's at the workbook module level. I then went back it use the "hide" function and it didn't work.
I created a couple new worksheets in the same workbook, and the same VBA works fine on them. I created a copy of the "non-working" worksheet and added the VBA to the copied sheet and it fails to work.
If I create an error in the VBA (like: Column("L").EntireColumn.Hidden = True), I get an error when I change the (non-working) worksheet - so I know it is "trying to run", at least sort of.
View 6 Replies
View Related
Jun 9, 2009
I have written a number of UDFs, which work fine in my spreadsheet. Two of these are more complex, and I'm having a few problems...
When I run any VBA routine (i.e. control passed to VB), these two UDFs return #VALUE! errors, but I can't see why. As these are Functions, I can't think of a way to test where the error is being generated, like I would in a Sub
When I return to Excel, any recalculation fixes the problem, which makes me think something is declared incorrectly. All named cell references relate to other VBA functions, calling the relevant row / column numbers, and I think these are all correct. I've tried removing AS INTEGER etc, to avoid type mismatches, and adding APPLICATION in front of worksheetfunction...
Function precedentStart(Target As Range) As Integer
Application.Volatile
With ThisWorkbook.Sheets("sheet1")
Dim splitPrecedent As Variant, lookupI As Integer, rowI As Integer
precedentStart = .Cells(Target.Row, colOrigStart).Value
splitPrecedent = Split(Target.Text, ",")
For i = LBound(splitPrecedent) To UBound(splitPrecedent)
rowI = WorksheetFunction.Match(splitPrecedent(i), .Range("schedRefs"), 0)
lookupI = .Cells(rowI, colAdjEnd).Value
If lookupI > precedentStart Then precedentStart = lookupI
Next i
End With
End Function
View 9 Replies
View Related
Nov 28, 2009
I can't seem to find an answer for this, but as an alternative, on Worksheet Activate the code runs and places a 1 in Range("A1") and therefore the next time the macro will not run.
This works fine for me, but just thinking if somebody who doesn't know why the one is in A1 and deletes it this macro will run again on sheet activate.
Private Sub Worksheet_Activate()
Dim LR As Long
Dim rng As Range
If Sheets("Charts").Range("A1").Value = 1 Then Exit Sub
LR = Sheets("POD").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Charts")
.Range("I129").FormulaR1C1 = "=ROWS(R1C1:R[-128]C[-8])"
Set rng = .Range("A129").Resize(, 10)
rng.Copy rng.Resize(LR - 4)...............
View 9 Replies
View Related
Dec 12, 2008
I created this spreadsheet to assist with pricing for my towing company. I struggled with getting the dropdown lists and combo boxes to work, and I created and deleted many of them before I got it to work like I wanted, but I think I left a mess. It runs slow, showing a delay when I click on different radio buttons, and there is an exclamation point in the Excel icon in the file directory. Could someone take a look in the code structure and tell me if there are any opportunities to clean it up. RateCalcApp.xlsm
View 2 Replies
View Related
Apr 3, 2009
In VBA I use this command, which runs *.vbs file that is used as a message box:
View 3 Replies
View Related
Jun 10, 2014
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
View 7 Replies
View Related
Dec 23, 2006
why the On Error GoTo statement only executes the first time an error occurs in the following
On Error GoTo CubeNotFound
For x = 2 To TotalRecords
Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'Other actions are located here, but have been removed. These actions take place if the Find is succesful.
CubeNotFound:
Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")
Next x
I have that statement there because if WS ID is not found then VB generates an error that the user won't be able to interpret, and also stops the code. I want VB to continue to the next record if one is not found.
View 9 Replies
View Related
Feb 5, 2008
Running Windows XP; Excel 2003
I have a fairly simply macro.
It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
View 9 Replies
View Related
Feb 15, 2008
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
View 9 Replies
View Related
Nov 13, 2008
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
View 9 Replies
View Related
Jun 30, 2009
I want to display a progress bar for when those macros are run. I've read many different ways to do this when I search it on google but I would prefer to use the cleanest method where I can simply call the appropriate procedure for each different macro, without changing any of the other code of the progress bar.
View 9 Replies
View Related
