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?
I am writing an XLA in XL2002 which will act on a number of legacy and new spreadsheets. During the execution of the XLA I set cell values.
i.e. m_rRange.Cells(j, i).Value = m_sInVal(j, matchIndex)
This then triggers the onChange event of the worksheet. Unfortunately, for one of the spreadsheets that this needs to work with, this causes an error. The error is trapped within the spreadsheet itself but the sub End is called which halts all VBA code execution.
My problem is that the XLA has not finished working and gets terminated early. I cannot capture this using an onError statement as it is never triggered (the spreadsheet code stops the execution and control never returns to the XLA).
I can disable the events using Application.EnableEvents but this means that none of the onChange events are called which is also not desirable.
So my question is: is there any way of disabling the termination of code using the End() statement, or of detecting when it happens to stop it?
I'm trying to create a new worksheet just in memory.
Private Sub test() Dim wsheet As New Worksheet Dim a As Variant a = ActiveSheet. Range("A1").Value wsheet.Range("A1").Value = a End Sub
When I run this, the like "wsheet.Range("A1").Value = a" gives the error <Class does not support automation or does not support extended interfaces. I could use that vitual worksheet (I call) to do functions like sorting filtering and other things without affecting the actual data, and get only the results. Is there a way to create such virtual worksheets.
First of I have a workbook, that runs several macros. All works fine. However I need to better secure the workbook. I realize that there is no guarantee on the security with excel but am hoping for something to solve the problem stated below.
When a user (with high security settings) opens the workbook in question, I would like the workbook to CLOSE out if the user chooses to disable macros when the asked by excel.
When looking at a spreadsheet with many columns, it would be nice of the row of the cell I have selected would remain highlighted in yellow, so that as I scroll across rows it's easy to view the data associated with the selected cell. Of course, if I select another cell, I would want the highlighting to change to THAT row. I'm guessing this would have to be in a this.workbook change event.
When I run a webquery to download information from online I have found the common 1004 error to occur very often however I have found that if I delete my temporary internet files prior to running the webquery, this problem does not occur. I therefore require an automated function to delete the temporary internet files however the simple 'Kill' statement does not seem to work.
I have a VBA application that uses the IE Application object many many times through a loop, and then repeats. So basically it is running continuously, and opening invisible IE window every few seconds.
I have code in there that 'kills' all IE instances between each iteration so that the number of windows doesn't pile up, but after awhile the temporary internet files, cookies, and history build up too.
Is there any VBA code that will clear those three things out?
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)
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.
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.
Below is the code I currently use to do the following which creates service reports.
When you click on the command button you can choose any XML file you want to import. This file is copied to a temporary worksheet where i selectively choose cells to populate textbox's which later populate my reports. What I want to do is the same exact thing but with a text file. Not all text files are the same length so it needs to be able to have a range. Here's my working code.
For Each SheetName In Array("Maintenance Data Sheet") With Sheets(SheetName) .Range("B127") = "" .Range("U127") = "" .Range("AJ127") = "" .Range("AV127") = "" .Range("AY124") = ""
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
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 spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
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?
Is there a way using VBA that will allow me to disable a COM Add-in when I open a workbook without me (or the user) going through the steps to remove it first.
The reason for my asking is this: We have installed SAS Add-in 2.1 to Excel 2007. So this brings up SAS under the Add-Ins tab. But then when my program executes and it sees I use command bars, it adds my command bars under the SAS menu. I am trying to make the interface as clean as possible for the user, so they only see my command bars or menus.
Also, if we were to install the SAS Add-in 4.2 to Excel 2007, I have read in a SAS paper that SAS Add-in 4.2 creates its owe tab on the ribbon instead of inside the Add-in tab. So I would be able to use xml to make the SAS tab visible="false".
