I have a work book that contains 10sheets. considering one as main sheet remaning as sub sheets. I want to open main sheet from all other remaining sheets using a common button.when I tried normally is shows compile error: Ambiguous name detected. Any other way to solve it.
How do I run this I can not get it to work I keep getting errors this is the way I have it for now.
Private Sub ThisWorkbook_Open() MY_MONTH = Month(Now()) + 5 With Sheets("Master List") .Unprotect ("123") .Columns("A").Locked = False .Columns("A").Hidden = True .Columns("T:IV").Hidden = True .Rows("265:65536").Hidden = True .Columns("E:Q").Locked = False .Columns("E:Q").Hidden = True .Columns(MY_MONTH).Hidden = False .Columns(MY_MONTH - 1).Hidden = False .Columns(MY_MONTH - 1).Locked = True Dim ws As Worksheet For Each ws In Sheets([{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC","2008","Read Me","Record"}]) With ws .Protect ("123") End With Next End Sub Compile error: Expected End With
My workbooks contains 2 worksheets with data and autofilters on for each column in use.
When my workbook opens I am trying to reset the autofilters of each worksheet in the workbook and to filter the data according to one criteria in one column.
This is what I have that works to reset each worksheet but I haven't been able to figure out how to subsequently filter each worksheet.
Private Sub Workbook_Open() Application.ScreenUpdating = False For Each w In Worksheets w.Unprotect If w.FilterMode Then w.ShowAllData w.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFiltering:=True Next w Application.ScreenUpdating = True End Sub
The autofilter sits in A3 to N3 and I am trying to filter according to column M (i.e. 13th column)
I've created an add-in that runs a simple macro. The problem I have is that when I run the add-in it opens the workbook I originally created it from - is this what I should expect it to do? How do I stop this from happening?
Also, do I need to have the workbook from which I created the add in located in the same place on every machine I want to use the add in on? At the moment if I move the original workbook the add-in won't work.
I have a workbook on a share drive that is used by multiple people. In the event that someone leaves the workbook open after using it, I have a timer function that pops up a splash screen after 4 minutes of inactivity which states, "This workbook will close in 1 minute if there is no further activity". If there is no further activity in that minute, the workbook closes.
The code works fine...unless the workbook is manually closed in that minute between the splash screen and when the timer would have closed the workbook. If that happens, the workbook closes normally and then briefly reopens and closes a minute later when the timer would have closed the workbook. Is there any code to prevent this?
I have the following code in a module that I would like to initiate when the workbook opens:
Code: Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" ( _ ByVal lpBuffer As String, _ ByRef nSize As Long) As Long
Public Property Get ComputerName() As String
[Code] ........
In a worksheet, I have =compname() in cell A1 and =hdserialnumber() in A2. What is the trick to make the values in these cells appear as soon as the worksheet opens?
I have this data sheet with 7 personnels name in it. What I require is that upon clicking on the name of the person, the profiles that are attached to this person will open up.
Before any name is clicked, these sheets should not be visible to the user and upon clicking on the other names, the sheets that was previously open will disappear.
I need to set the "Sheet1" to be the sheet that always displays first, regardless of what sheet was active when last saved.
The reason is that I have a sheet with instructions on it as how to use the Workbook, including "You must Enable Macros" etc. When Macros are enabled, it's all good, as most people use a USERFORM which I have coded to set "Sheet1".Select and save their work upon closing using the USERFORM.
BUT when the advanced users modify other Sheets directly without using the USERFORM, and "SAVE" and then "Close" , the next time a user opens the Workbook (If they do not enable Macros) it opens to the sheet the advanced user Saved on. Thus killing my instructions page and putting the exact people I did not want messing around on certain sheets, on those sheets, fully bypassing the USERFORM created to avoid the problem.
I can force the workbook to switch to "Sheet1" and Save and Close, every time someone closes or EXITs, BUT I quickly realized when testing that any mistakes made are then permanent because you cant EXIT without Saving.
So. Is there a setting somewhere (Not Requiring Enabled Macros) to force a Workbook to ALWAYS open "Sheet1" first, regardless of what Sheet was last saved in the Workbook?
I have written a very basic Macro code to retrieve a photo off of my server. I want it to run this macro when a person has selected a certain item off of a drop down list. The problem I am having is that when I try and run the macro manually or when the workbook opens I get a an error message that reads "Compile Error: Invalid outside procedure". I click "Okay" and it highlights this portion of the code
I have a workbook where the sheets are all protected and I want to stop users scrolling up or across beyond the limits of the input areas. I have used the following code (or variations of)...
Private Sub Worksheet_Activate() Me.ScrollArea = "A1:G32" End Sub
This works fine except that when I first open the workbook, the first sheet displayed can still be scrolled. As soon as you move to another sheet and then back again, it is then OK.
It appears that the Worksheet_Activate code does not execute when the workbook is first opened.
I am trying to find a way to automatically do a query and dump the data into a sheet when a previously created workbook is open. I know how to get the data (use the Tools, Import External Data option to retrieve data from Access) but what I don't know is how to make it do it automatically when the workbook is open.
I have a problem, where every time I close or open any workbook, Excel prompts me to "Enable or Disable Macros" in one particular workbook, wether I have tried to open it or not. In the said workbook, I have a macro that automatically updates a pivot table upon selecting the worksheet where the pivot table resides.
The code for it is as follows:
Sub UpdateIt() Dim iP As Integer Application.DisplayAlerts = False For iP = 1 To ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iP).RefreshTable Next Application.DisplayAlerts = True End Sub
Private Sub Worksheet_Activate() Call UpdateIt End Sub
There is something in the code that calls UpdateIt any time a workbook is opened, closed or selected.
I am trying exactly the same things stated in here Open two workbooks at the same time as stated in the other thread's answer I would love to "just use the Workbook name IF both Workbooks are in the same folder." both files are in the same folder and this is the code that I am trying to use
Private Sub Workbook_Open() Workbooks.Open ("toto1234.xls")
and it does not work any one explain me what I am doing wrong?
I'm trying to introduce some VBA code to an existing workbook that will automatically sort column A alphabetically on Sheet 2 (Summary) (excluding rows 1 & 2 as they contain headings) whenever the workbook is opened.
At the moment this code doesn't automatically run and when I run it manually I get a run-time error '91' the first time I try and when I run it for a second time (again manually) it works.
The code I've got at the moment is:
VB: 'The following code will run the sorting subroutine when the workbook is opened: Private Sub Workbook_Open() Call SortByWave End Sub 'And this subroutine will enable the autofilter functionality, clear all existing filters and then filter the data on column A: Sub SortByWave()
When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?
I have a program that uses the Document Open event to display a custom form. This program is being used on about 50 computers for the past 8 years with no problems. One user has a problem now. They can open the program once and the code fires. But when the user tries to open the same file a second time, the "Microsoft Excel has encountered a problem and needs to close" dialog box is displayed. The document that is recovered has no vba modules and no code in the Document open event. I've uninstalled and reinstalled Office Professional. Shut down all firewalls.
I currently have the following code that copies a range opens notepad pastes the range opens save dialog and types the file name. The problem I have is with overwriting the existing file.
So I have a macro which is running slowly, though I can't figure out why. I don't remember adding/subtracting from it. All code prior to this macro being called runs fine...So this part below runs fine:
Private Sub Step_3_Click()
Dim YR As String YR = Range("C24")
Dim DT As String DT = Range("C27")
Dim MNTH As String MNTH = Range("D23")
For Each wb In Workbooks If wb.Name = "Top100gen2start.xls" Then MsgBox "You need to close the Top100gen2start.xls file before proceeding.", , "Close Top100gen2start.xls file" Exit Sub End If Next
If CheckBox1.Object = False And CheckBox2.Object = False And CheckBox3.Object = False And CheckBox4.Object = False And CheckBox5.Object = False Then MsgBox "You didn't select a sector report to run!", , "Select a sector report" Exit Sub End If
If CheckBox1.Object = True Then Call FullBlown If gameover = 1 Then Exit Sub End If End If
Once I call that Fullblown macro (or any of macro similar to it in this process), the whole things slows WAY down (I mean as soon as it is called)...Not sure what I'm doing wrong, but here's the called macro:
Public Top100Check As Integer, gameover As Integer
Sub FullBlown()
Application.ScreenUpdating = False
' Dim x As Date
' x = Format(Date, "mm-dd-yy")
On Error GoTo Jump ' Workbooks("PERSONAL.XLS").Close Jump:
I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.
Private Sub UserForm_Activate() Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet Dim rngFound As Range Set wsCB = Sheets("Current Bonds") Set wsLL = Sheets("Landlords") Set wsBond = Sheets("Bond") TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy") RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy") Application. ScreenUpdating = False 10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number") strFindMe = MyBond With wsCB Set rngData = .Range("c13:c490").......................
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String) Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1, , text Close #1 j = InStr(1, text, strflag2) i = InStrRev(text, strFlag1, j) + Len(strFlag1) LastUser = Mid(text, i, j - i) End Function
i want a macro that that 1+2 and with "vlookup" out the cells in "total". i start to record the macro but ... it's not work at all. i want to put an input box that ask the user "what month and year" and then put the relevant file in the vlookup.
I am unable to add which user made the change to the specific cell. The code used is:
Dim vOldVal 'Must be at top of module Private Sub Worksheet_Change(ByVal Target As Range) Dim bBold As Boolean If Target.Cells.Count > 1 Then Exit Sub On Error Resume Next With Application . ScreenUpdating = False .EnableEvents = False End With If IsEmpty(vOldVal) Then vOldVal = "Empty Cell" bBold = Target.HasFormula With Sheet1 . Unprotect Password:="Secret" If .Range("A1") = vbNullString Then .Range("A1:E1") = Array("CELL CHANGED", "OLD VALUE", _ "NEW VALUE", "TIME OF CHANGE", " DATE OF CHANGE") End If..................................................
How can I prevent a user from opening another workbook in an Excel session "from the outside"?
I'm fairly new to VB. I'm developing (in VB 2003) a simple but SECURE Excel environment which will allow a user to update a hidden Master_Records workbook. I need to keep the user's Excel session secure...for example, I've disabled all Excel Toolbars and Command Buttons, effectively preventing the user from doing anything except filling in some cell values and clicking on a few custom buttons in the worksheet. But how can I prevent the user from opening another workbook into the active session from his desktop and introducing some malicious code into the session via that route?
One respondent in another forum (the only one, in fact) suggested that I look into "instantiating workbook level events" so that I can detect when other workbooks are open. I'm not sure what that means, is there someone here who could give me some guidance into that solution?
I wrote a code that is suppose to save the workbook every time a change is made but so far I have been unsuccessful at getting it to work. The workbook contians 7 worksheets.
This is the code I wrote to try and accomplish this task ....