I would really like to know if there is a way that when my workbook opens if the following can happen?
- Not display the tabs (NOT "HIDE" the sheets, just not be able to view the tabs themselves)
- Go to full screen (not display ribbon)
Now this is something which I dont even know if it can be done, but its something which I would use, a lot. When clicking on a button, like a normal button from the control toolbox, is it possible to open a menu from this? So I click on the button i made called "accounts" and this opens a menu next to it with different options.
View 5 Replies View RelatedHow 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 need to stop the calculations when the workbook opens. I have tried
View 2 Replies View RelatedI 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'm new to VBA so my i'm having alot of problems figuring out simple stuff.
Below is my script and when i run it, the dates turns out incorrect.
I noticed this only happens to dates that are before 13th of each month.
Example,
1st May 2008 ( 01/05/2008)
will turn up as
5th Jan 2008 ( 05/01/2008)
However when i manually open the file Todays trades.csv
The date looks just fine.
Is there some problem with using VBA to call up the file?
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
View 5 Replies View RelatedIs there a way of displaying a message box on the first of every month when a workbook opens ?
View 2 Replies View RelatedI 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.
How do I get round this?
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.
View 2 Replies View RelatedI 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.
View 7 Replies View RelatedI 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()
[Code] ......
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?
View 2 Replies View RelatedThe same two workbooks open when I open any other workbook. They also open up when I open Excel by itself.
View 9 Replies View RelatedI 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.
View 9 Replies View RelatedIs there any way to select things like tools - options across all worksheets in a workbook? I often want to suppress display of zeroes in an entire workbook, but so far the only way I've found to do this is to go to eash worksheet in turn and go thru the procedure on each one.
View 3 Replies View RelatedI 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.
Range("A1:A202").Select
Selection.Copy
Shell "notepad.exe", vbNormalFocus
SendKeys "^V"
SendKeys "^s"
SendKeys "Total_IEDs_Hour_Of_Day_2009.xml"
SendKeys "{TAB}"
SendKeys "a"
SendKeys "{ENTER}"
Everything works fine to this point. Then it opens the do you want to overwrite dialog and I cant get it to hit yes.
I have a save button running a macro that validates before saving. Is there a way to remove all other save options (alt f a, ctrl s, the toolbar save button, etc.) but just from this one workbook?
A while ago I tried playing with a code that did this but it did it through out excel.
I have an Excel 2007 Workbook that has a variety of different reports. I’m trying to find a way that will allow me to reset reports back to original setting after the report has been completed. Sometimes a report may take a couple days to complete, so closing and saving is a must. Any thoughts how I may achieve this.
View 9 Replies View RelatedI'm trying to limit the controls users have on a specific workbook. Problem is I would like for them to have this workbook open throughout the day and if I look all the controls then they will be locked out of these controls on other seperate workbooks they open. The only reason I ask is because of some awesome code I just found by Dave Hawley. The code is...
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application
. CommandBars(1).Enabled = Not Sh. Name = "Sheet1"
.CommandBars(" Formatting").Enabled = Not Sh.Name = "Sheet1"
.CommandBars("Standard").Enabled = Not Sh.Name = "Sheet1"
.DisplayFormulaBar = Not Sh.Name = "Sheet1"
End With
ActiveWindow.DisplayHeadings = Not Sh.Name = "Sheet1"
End Sub
This works great with one exception. It will only block the menus for one Sheet.
In my workbook I have 8 sheets and would like to apply this to 4 of them. Is there any way to modify code to hide menus on mores than one sheet. Also while I'm in the neighborhood. Is there a way to hide the sheet tabs for this workbook only. So once again if a user opened another workbook they would be there?
I have a date displayed in a controlbutton caption that fails to refresh each time the worksheet is opened.
Q1: How do I run the sub below each time the user opens the worksheet?
Q2: Is there a better way to clear the contents of a textbox?
Private Sub ClearForm_Click()
ActiveSheet.Unprotect
ActiveSheet.Range("C4,C6,C8,F4,F6,F8,F25").ClearContents
ActiveSheet.Range("I4").Value = 0
ActiveSheet.Shapes("Text Box 12").Select
Selection.Characters.Text = ""
MailDate.Caption = Range("H11").Value
ActiveSheet.Range("F4").Select
ActiveSheet.Protect
End Sub
When I double click an excel spreadsheet in windows explorer it opens spreadsheet then closes it then opens it again.
This doesnt happen if I have an instance of excel already running.
I thought it might be to do with file association and the open with option.
I have a project were i have to make a macro and place in a workbook such that as soon as i open that excel file
1. excel workbook is opened in New Excel window
2. Ribbon is hidden for this new window.
So when user closes this excel window there is no problem with other workbook ribbon, as there are open in different window
I have tried with code stated below it hides the ribbon but new excel window is not open
Private sub Workbook_open()
Application.ExecuteExcel4Macro "Show.toolbar(""Ribbon"",False)"
End Sub
This is a follow-on to a great solution provided by Shg and Rylo. Here is the link. http://www.excelforum.com/excel-prog...new-sheet.html
My users have run in to an interesting issue with the file. Each time the files opens it has the same 5 questions selected. What I need the code to do is randomize the questions each time the sheet is opened. Basically, the users seem to have a problem using the "make quiz" button, so they have requested that I make sure that the quiz randomizes each time it is opened.
