I am trying to find out more info regarding the BeforeSave workbook event, need example of a short procedure that would prevent the user from saving the workbook with the current workbook name, and would automatically force the file to save as a web page? I'm sorry that I don't have any examples of code that I'm working with, and it feels pretty juvenile to just ask someone to write code for me. But I haven't been able to get past step one with this.
In the situation where I shut down my model (i.e. not just press Ctrl+S, but shut it down), the following code saves the model twice. I only want it to save once. I really can't see why it saves a second time.
NB: I know if you set Cancel to True in the BeforeSave event, it should prevent the model from saving after the event, but even when set to true in the below code, it still saves...
Private Sub Workbook_BeforeClose(Cancel As Boolean) bIsClosing = True 'Given we shut model, this public bIsClosing is set to true... End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wsArray() As Variant Dim iCnt As Integer Dim wsSht As Worksheet Application. ScreenUpdating = 0 Splash.Visible = True 'Splash is codename for warning spreadsheet, this is left 'visible while all other sheets are to be hidden.......................
I have a spreadsheet that I'm working on and I'd like to include a beforesave macro to format the spreadsheet. Basically I need a macro to select all rows with data in column 'a' and add a thick border to the top of the cells. Ideally the border would stop at column 'i' but that's not essential. I've attached an example of what I'd like. I've included 2 sheets 1 called 'before' and 1 called 'after'. They show what I'm after.
I have the following code that (should) either save the file with a generic name if a value exists in a certain field, or else save the file as the contents of the value in the field, with a message box appearing in either case. The problem is, the message box appears twice (not once) and then Excel crashes! Can anyone tell me what's going on? The code is:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FSR As String FSR = Range("I7").Value If FSR = "" Then MsgBox "Service Report Number not entered." ActiveWorkbook.SaveAs Filename:="Service Report Navilas.xls" End If
If FSR "" Then MsgBox "This action will save the file as the Service Report Number." ActiveWorkbook.SaveAs Filename:=FSR & ".xls" End If
the following code work on both PC and mac? It works fine on PC, but on mac the BeforeSave and BeforePrint code does not work and the SaveASheet code gets as far as opening and pasting the sheet but then is unable to save automatically.
Private Sub Workbook_BeforeClose(Cancel As Boolean) SaveASheet End Sub Private Sub Workbook_Open() Sheets("Open").Activate End Sub Sub SaveASheet() Dim fName As String Dim myPath As String Dim sht As Worksheet #If Mac Then..........
Example below is in my macro and when my peer is running this worksheet which has Workbook_Open() enabled in "ThisWorkbook", the outcome is that he can not successfully save this workbook because he doesn't have the folder and sub directory in his C: drive.
How do I code it such that in the event the user does not have the specified folder and sub-foldername, it will automatically create one for him and save it as "C:Spare PartsCritical ListStocking Status.xls".
I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.
here is the code I have on open that works fine: -
Private Sub Workbook_Open() If Environ("username") = "Bob" Then Sheets("Sheet1").Visible = True Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = False Else Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True End If End Sub
I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!
Here it is: -
Private Sub Workbook_BeforeClose() If Environ("username") = "Bob" Then Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True ThisWorkbook.Save Else Sheets("Sheet1").Visible = False Sheets("Sheet2").Visible = False Sheets("Sheet3").Visible = False Sheets("Sheet4").Visible = True End If End Sub
So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)
when I go to ThisWorkbook and click the left side dropdown at the top of the code window, I can't see the word "Workbook" appear. What I can see is only (General) and there is no other choices.
Other worksheet and application event work fine.
attached is a screen shot when i try to add a workbook event.
In R1 I sometimes place a cell reference that I want the workbook to immediately go to when it is next opened. I have the following code which does not work as expected (Ignore the necessary .Select parts):
Code: Private Sub Workbook_Open() Sheets("MSCI Asia Data").Select Range("R1").Select If Not IsEmpty(ActiveCell) Then Application.Goto reference:=Range(Range("R1").Value), scroll:=True End Sub
Currently R1 contains the value R91 but when the workbook opens, it is not going to the cell R91 in the required worksheet.
Have some code in the workbook open event, which when I run when the workbook is already open works fine, however when triggered when the workbook is actually opened it fails on the first line.
Code: Private Sub Workbook_Open() Dim lngLastRow As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim ws3 As Worksheet Dim X As Long Dim Y As Long
'Define Worksheet
Set ws1 = Sheets("Sheet1") Set ws2 = Sheets("Sheet2") Set ws3 = Sheets("Sheet3") Sheets("Sheet1").Activate
I have a workbook change event that when fired checks criteria to see that the sheet is complete. If not complete, the user must complete information on the sheet before continuing. The problem is that some of the "stuff" the user must do requires macros that fire and gain access to other sheets. Thus the "trap" I have set to restrict the user to just the one sheet has backfired on me restricting the user to just the one sheet. Is there a way that the workbook change event (or any other workbook event) be temporarily disabled until activated again?
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:
The use of this userform is to find a customer reference number. Im using two combobox's and a textbox.value that are populated from another workbook.
Though the way it is at the moment it opens the workbook and closes the workbook every time a new value is set to one of the combobox's.
I want to open the workbook on the useform initialize and do everthink the useform need from it. And then on the userform terminate close the workbook. Or somehink to this equlivent so this process of finding the customer referance number goes faster.
I have a boolean toggle for disabling and enabling a Workbook_Change event.
Right I invoke macros to turn the Workbook_Change event on and off and I don't know what state it's in.
Two Questions: 1) (required) I would love to be able to visually know if it's enabled no matter which sheet I am on 2) (bonus) Be able to change the state with a persistent interface (e.g. perhaps the Ribbon?) no matter which sheet I am on.
I have a Shared Workbook that 10 or 15 users are in and out of all day. In Column A on a few sheets I have a Before Double Click Event that launches a userform. The macro works for all users except this one person. I have tried several things:
1. I closed the file and Reopened it, to make sure that the user did not disable macros.
2. I checked the file on other users computers to see if the file was working properly (It was.)
3. I went to Options>Trust Center>Trust Center Settings>Macro Settings and Enabled All Macros on this User's system
None of these actions corrected the issue.
The purpose of the BeforeDoubleClick Event is to store Columns A:E data and then let the user add more information through the form. Once the User fills out the UserForm. A:E is transferred to one of a number of sheets depending on criteria in the UserForm. A:E is transferred along with the new information that the user has entered in the UserForm.
Another symptom is that on other computers when the Before Double Click Event is activated the Userform launches and the cursor does not appear in the cell until after the User Clicks a Command Button to Update Data on the Userform. On this individual's computer the cursor does appear in the cell without the Userform launching....
I have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using
Private Sub Workbook_Open() SaveOnOpen End Sub
where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?
why this code does not work when the worksheet is changed between range "B1:F5"?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:F5")) Is Nothing Then With Range("B1:F5") Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value End With End If End Sub
I have many worksheets in a workbook that need to be saved if a user changesanything on them. These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim sht As Object For Each sht In ThisWorkbook.Sheets If LCase(Right(ws.Name, 2)) = "sd" Then MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save End Sub
It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)
I'm trying to combine 2 sets of code that I have searched other threads for.Both use the OnTime code to trigger events after 5 and 10sec. My problem is that the workbook won't close correctly and keeps re-opening on the refresh event. I understand both events need to be passed to the dTime variable, but I am obviously doing something wrong. I will include the code as I'm sure its something obvious;
Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.OnTime dTime, "Sort", , False If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False On Error Goto 0 End Sub
Private Sub Workbook_Open() Run "RefreshIt" Application.OnTime Now + TimeValue("00:10:00"), "Sort" End Sub
Public dTime As Date Sub Sort() dTime = Time + TimeValue("00:00:10") Application.OnTime dTime, "Sort" Range("A6:M10").Select Selection.Sort Key1:=Range("I6"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B2").Select End Sub
Sub RefreshIt() Sheets("Text").Range("A7:F38").QueryTable.Refresh dTime = Time + TimeValue("00:00:05") Application.OnTime dTime, "RefreshIt" End Sub
I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.
The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.
The code is below;
Private Sub Workbook_Open()
DoEvents
Dim Res1 As VbMsgBoxResult Dim GovRng As Range
For Each GovRng In Sheets("Map").Range("GovernanceMembers") If GovRng.Value = Application.UserName Then Goto 111 Next GovRng
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
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..
Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my
I've created a macro with a custom dialog box, but I don't know how to make the transition from when I make the dialog box pop up, the user enters the information, then they click "Continue" or "Cancel" or whatever it may be, how to do I make it happen from there out?
Do I make the command buttons a boolean and if they click it's true? How do I make it work?
