Sheet Opens Fine But Behind Other Sheets?

Feb 18, 2013

I have the following macro which basiclly acts as a refresh button on a read only sheet:

Code:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:05"), "OpenMe"
ThisWorkbook.Close SaveChanges:=True
End Sub
Sub OpenMe()
End Sub

The sheet is read only as it is used by many people and it prevents them damaging the sheet. The sheet is updated regularly and as many people who use the sheet have little excel knowledge a refresh button was required. The macro is attached to a button that when clicked, it closes the sheet, then reopens it meaning any updates I add to the sheet can then be seen by everyone else once they click the button - much like 'Refresh'.

So, the code above works fine. Click the button, the sheet closes, 5 seconds later reopens. However, when the sheet reopens, any other excel sheets that are open seem to be at the front and the sheet that has been 'reopened' is behind them. Is there anything I can do to make the macro not only close and reopen the sheet, but when it does reopen, bring it to the front so that it is visible straight away without having to click Window and then select the appropriate sheet?

View 1 Replies


ADVERTISEMENT

Force Specified Sheet To Display When Workbook Opens

Jun 11, 2013

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?

View 8 Replies View Related

Double Click On A Total It Opens A New Sheet

May 15, 2009

i am working on a pivot report and it works fine so far, but the only thing i am trying to do here is when duble click on a total it opens a new sheet with the report needed but it names the sheet as sheet1 and so on (sheet2,3,4...)

is there a way when i duble click a total it automaticly names the sheet for me?

View 9 Replies View Related

Code That Copies A Range Opens Notepad Pastes The Range Opens Save Dialog And Types The File Name

Nov 6, 2009

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.

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.

View 9 Replies View Related

Text To Columns Fine Except When Using VBA

Jan 13, 2009

I have a column on my sheet, Column B which is full of dates. I need to do text to columns because the way the date comes in doesn't make it easy for my macro to work with.

View 2 Replies View Related

Macro Runs Fine In Debug But Not From ComboBox

Feb 24, 2009

I have a workbook that contains 9 worksheets. Four of the worksheets have the same row lables in column B and must always be the same. Three of the worksheets are fed from the 4th sheet so that the integrity of the lables is maintained.

The user can change the lable value to meet their needs and they can insert or delete rows (within limits) as they see fit.

The attached macro "Sub Delete_Row_All_Sheets()" works fine when I run it from Debug (F8).

However, when I run it from Forms.ComboBox the macro returns to the "y = Application.InputBox("Enter The Row Number You Wish To Delete", _" screen. If I select cancel, the results I anticipated occur but I don't want the user to have to assume this will happen.

Why does the macro return to this screen when executed from the ComboBox but not when executed from Debug?

View 14 Replies View Related

Excel Code Works Fine But Error When Called From Access?

Apr 11, 2012

This code works fine when I run it in Excel.

Code:

Sub RemoveCodeAndSave()
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _

[Code]....

I get the error "Run-time error 440: The specified dimension is not valid for the current chart type" why I am getting this. The macro does everything I want it to, except for throwing the error at the end.

View 3 Replies View Related

Macro Doesn't Work On Button, But Fine From Macro Menu!

Dec 15, 2008

I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View 12 Replies View Related

Sorting Sheet That References Data From Other Sheets (Google Sheets)

Jan 25, 2014

[URL] ....

I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.

View 1 Replies View Related

Merging Sheets / Copying Cells From X Sheets Into 1 Sheet

Feb 22, 2013

I have merged 336 individual spreadsheets into one book, now I want to merge the data in all the sheets into 1 individual sheet. All the sheets have the same size and range, I need to copy a constant range(row,column) from all the different sheets into one.

What VBA functions to use???

View 4 Replies View Related

Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets

Dec 26, 2009

I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View 9 Replies View Related

Copying 2 Columns In One Sheet To All Other Sheets (except 2 Sheets)

May 25, 2014

i wanna copy C and D columns in Sheet1 and insert these 2 columns (copy + insert copy cells function, not copy + paste) to all other sheets except Infopage sheet

View 14 Replies View Related

Run Sub When Worksheet Opens

Apr 24, 2007

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

View 9 Replies View Related

Run When Workbook Opens

Oct 4, 2008

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

View 9 Replies View Related

Opens Spreadsheet Twice

Dec 24, 2008

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.

View 9 Replies View Related

VBA That Opens Excel In New Window

May 3, 2014

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

View 2 Replies View Related

Randomize When File Opens

Sep 14, 2009

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.

View 7 Replies View Related

Options When Workbook Opens

Jan 31, 2010

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)

View 6 Replies View Related

Tracking Who Opens Spreadsheet

Dec 19, 2007

I have a spreadsheet that can't be password protected but this doesn't mean just anyone should be accessing it. Is there a way to record who opens it and when? Ideally I could place some code into the spreadsheet that recorded on a sheet who has opened it and when. This will detract certain nosy people from accessing it!

View 9 Replies View Related

Autofilter When Workbook Opens

Jul 16, 2008

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)

View 9 Replies View Related

Add-in Opens Workbook It Was Created In

Oct 27, 2006

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.

View 9 Replies View Related

Run A Macro When Worksheet Opens

Feb 1, 2007

I have worksheet with a macro and currently if I run the macro using the play button and then select the macro it runs fine but what i want it to do is run when i open the workbook automatically.

View 3 Replies View Related

Select The File And Opens

Feb 28, 2007

I have a function which selects a file and opens it.It is part of a code.

Sub Cpy()
' Macro recorded 1/16/2007 by PZCHFR
Set PresentWorkBook = ActiveWorkbook
FSel
Findlastusedrow
Cpy_Name
getmonth

End Sub

Function FSel()
filetoopen = Application. GetOpenFilename("Document Files (*.xls), *.xls", 1)
'Workbooks.Open Filename:=filetoopen
'Set OpenedWorkBook = Application.Workbook(filetoopen)
Set OpenedWorkBook = Workbooks.Open(Filename:=filetoopen)
End Function

When the user does not select any file and clicks "cancel" in the dialog box,this gives me runtime error .How do i make the code correct so as to handle the case where there is no file selected.

View 4 Replies View Related

Conditional Macro When Excel Opens?

Sep 28, 2013

I want to run a VBA macro when Excel opens if and only if the file name contains a specific string or if and only if the worksheet name contains a specific string. The file only contains one worksheet. For example, if the file name contains the string "SalaryData" or the sheet name contains "SalaryData", then I want to run a VBA macro to delete some of the confidetial information. Idealy, this would be done as the file is opening before it is displayed.

View 1 Replies View Related

Excel 2010 :: Opens New Instance For Each Fie

Dec 12, 2012

In the last couple of weeks my copy of Excel 2010 has started acting strange. Now when I double click an xls or xlsx file in Windows Explorer, it tries to open a new instance of Excel, whic means that it tries to reopen Personal.xlsm and Personal.xlsb, and finds they are already open, so it gives me an error message. Previously it perfectly happily opened the document in another window in the same instance, which is presumably much more memory efficient quite apart from the file locking problem mentioned. How do I get it back to the way it was before? Is this another of MS's famous overnight downgrades?

View 2 Replies View Related

Set Focus To A Textbox When Userform Opens

Jul 29, 2009

I'm creating a userform for editing membership records of a small organisation. So I want the focus to default to textbox tboxFind which is used for searching the data.

I've put tboxFind first in the tab order, and in subroutine Userform_Initialize, the last line is tboxFind.SetFocus

Using Excel2000 on Windows Vista, it works OK.

Opening the same workbook in Excel2000 on WinXP, no textbox has the focus when the form opens, but once the user has started to use the form, the focus defaults back to tboxFind, as it should.

So why is the focus not where it should be when the form opens in WinXP?

View 11 Replies View Related

Stop The Calculations When The Workbook Opens

Nov 24, 2009

I need to stop the calculations when the workbook opens. I have tried

View 2 Replies View Related

Workbook Automatically Opens After Closing

Jan 6, 2010

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?

View 4 Replies View Related

Calling Subs When Workbook Opens

Mar 7, 2012

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?

View 2 Replies View Related

Automatic Macro Run When File Opens

Mar 30, 2012

I need a code that will automatically run my first macro. The first macro already calls all the others. I just need everything to run as soon as someone opens the file.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved