i am tring to make any workbook i open when i writ in it, i want what i wrot to be in centerd
Private Sub Workbook_Open()
'*Center
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh. Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Next sh
End Sub ............
Is there any way to tell a userform to always open in the center of the excel screen? It always seems to open somewhere in outer space on multiple screen setups.
View 9 Replies View RelatedFollowing is what I want to do:
Draw a line from the center of the first cell to the center of another cell.
I have a worksheet in excel that I have hidden columns from AA right to the end. Which gives the user a nice worksheet with everything to the right of AA hidden in blue. What I would like to do is now centre the worksheet in the screen and have the same hidden blue effect to the Right of the worksheet in this blue.
View 1 Replies View RelatedSomehow I have changed a default setting that I cannot figure out how to change back. I have various templates setup in a "Forms" folder. Normally I go to My Documents, find the template file and double click on it, and the appropriate program (usually either Excel or Word) starts and opens a copy of the template as a document. What happens now is that the program starts and the template itself opens. I have tried going to My Documents and right clicking on the file and what appears to be happening is that the default (highlighted) option is "Open" instead of "New" which is what the default used to be. How do I change it back?--
View 7 Replies View RelatedI have both 2003 and 2007 excel installed. When I click to open a .xls file it opens it in Excel 2003. How can I make .xls files open in Excel 2007?
View 11 Replies View RelatedUsing Excel 2007
I have made a macro in a xltm book that will open the "Save As" dialogue but I would like it to default to xls file type instead of the user having to select it in the drop down box.
In addition to this I would like it to name the file based on a returned "=NOW ()" function from a cell (sheet1!G13) that has been mdms date formated which returns an unique document number each time for our reference.
Here's the code so far which works fine for the "save as" dialogue box part
I created a custom tab withthe UI editor into my personal sheet which is set as startup and I want to have this tab automaticly shown when it is opened.
So when the workbook is opened I would like the show the tab Andre rather than the home tab.
Is there any way of making the default directory location for application.getopenfilename work properly with shared locations?
Presently I use ChDrive then Chdir but I cant use ChDrive if workbook is opened from a link in email for example. Please see below
I have a VBA routine that asks the user to load an existing file on startup.
I want the window for the file selection to open at the same folder every time. (the folder is on a shared drive)
The workbook to run the code will always be located on this same share at a fixed location.
My present code builds the path to the desired opening folder by collecting the drive letter from the current working directory (which has to be done as everybody maps there shares to different letters here, its not guaranteed to be the same! )
then i ChDrive to this letter.
Then I ChDir to letter plus myKnowndirPath
This works fine, the window always opens at my required folder. But only as long as the user has navigated to the folder containing the spreadsheet and then ran it from there. Because that way a drive letter is available.
However i found that if i sent a link to the workbook or its home folder. And the user opens the link or a windows explorer from that link and then runs file from there.
Then it stops working beacause there is no local drive letter in the working directory path. i.e. working directory shows as
'network \shareddrive
ootafolderanother arget
instead of something like
J:afolderanother arget
I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
I would like the workbook to open to a specific worksheet everytime it is opened regardless of what worksheet it was in when file was saved. Can someone point me in the right direction?
View 2 Replies View RelatedI have a large project with many worksheets. Somehow I managed to change the default number format for new worksheets to Time. So... when I create a new worksheet and paste stuff into it I get everything in AM/PM. How can I change the default number format to Number?
View 2 Replies View RelatedI have set up a calendar control on a userform and got it to pop up when selecting one of 3 cells on a worksheet. The various bits of code making this work are below.
This code is attached to the Userform.
VB:
Private Sub Calendar1_Click()
With ActiveCell
.Value = Calendar1.Value
[Code]....
What I am struggling with is that I'd like to have the calendar that pops up in cell C18 (which is Date of Birth) default to starting on 1st January 1987 not todays date. Also, if there is a value in either of the 3 cells that use the calendar, then I'd like the calendar to display that date. If the cell is empty then todays date is fine for cells G3 and G26.
is it possible to write vb code to open the workbook, scroll through everysheet? extract the values from each worksheet?
let's just say, I only know the workbook name, but not the sheets of the name inside. So the vb code could scroll through every worksheet without knowing the worksheet name, only the workbook name(workbook.xls)
I am trying to write (but not very well as bit of a novice) some code to open another Excel workbook called 'Sweep Log'
once this is open copy certain data from my already open workbook to the 'Sweep log'. Save this log and then close it.
Once that has been done a Msg box to say 'data saved in sweep log'
result leaving my original workbook open
Private Sub CommandButton1_Click()
Dim oExcel As Object
Set oExcel = CreateObject("Excel.application")
oExcel.Documents.Open "R:ConnexSweep Log.doc"
oExcel.Visible = True
Sheets("Sweeplog").Select
With Selection
Range("A5:K100").Copy
Sheets("database").Select
With Selection
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Range("A5:J1000").Select
Need a code to select cell A1 on a worksheet called "Welcome"; this needs to either be on opening the workbook or exiting the workbook
View 7 Replies View RelatedI want to be able to run the following lines of code to edit all xy-scatter plots on worksheet that is currently open by clicking and running a .VBS file. This is the code that works in excel in my macro. I'm having trouble getting the .VBS file to execute and run correctly. I realize I don't need the dim statements for the .VBS file, and have been able to get it to run without errors, but it doesn't edit the chart.
Code:
Sub ChartLineWidth()
Dim c As Chart
Dim s As Series
For Each co In ActiveSheet.ChartObjects()
Set c = co.Chart
For Each s In c.SeriesCollection
s.Format.Line.Weight = 1
Next s
Next co
End Sub
I have an excel 2007 worksheet named "Invoice" that I would like to be able to navigate with using a command button that will open a separate File Folder or sub folder called "Report" that stores all my reports in for viewing.
My excel worksheet named "Invoice" and File Folder or sub folder named "Report" are located in the same sub folder that is named “Marketing Report”.
This report will be used on different computers and I’m sure different location / Path on their computer.
Workbooks.Open Filename:=ThisWorkbook.Path & "Report"
and
Private Sub CommandButton3_Click()
Dim fn As String
ChDir ThisWorkbook.Path
Report = Application.GetOpenFileName()
Workbooks.Open(Report)
End Sub
I also tried to record a macro but would not record going to file folder.
"Get rid of merged cells!"
You can read it often on this Board.
I will not open any debate on the subject, but just want to provide a solution to get rid of them automatically. Anybody who likes can refer to this thread.
WARNING
This code is changing layout.
1. Could take a while when you have sheets with large used range or a lot of merged cells. You can follow the status and can interrupt it if you want.
2. Save before executing. I'm quite sure it works good, but the result might be something else then you had in mind, especially when you are quite new to this.
Option Explicit
Sub Unmerge_CenterAcross()
'Erik Van Geit
'080808
'merged cells will be unmerged
'contents will be centered across merged area
Dim LR As Long 'Last Row
Dim LC As Integer 'Last Column
Dim i As Long
Dim j As Long
Is it possible to update open worksheet which would stay open from different computer without doing anything to it.
View 8 Replies View RelatedI know of the password protect feature to open a excel file or workbook, but does anyone know if this can be done for opening a worksheet.
I want to be able to view a worksheet after a password is entered, so therefore different worksheets have different passwords.
When I open my workbook, I'd like to go at the first empty row of the worksheet.
View 14 Replies View RelatedWhat would be the macro to open a specific worksheet upon startup?
View 2 Replies View Relatedi have one page with 6 different links, each link will open one worksheet , i want to set password to each link to open there worksheet , i can update my file if it's required .
View 14 Replies View RelatedI am currently putting together a Directory and want each of the 19 programs and the worksheets to be maximized when they are opened.
View 1 Replies View RelatedI've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it.
my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.
I have 2 worksheets in my workbook.
One is named "Link Menu" . . . The other is "Setup"
I have an object on "Link Menu" that can be clicked to run a macro.
On the Setup sheet, I have a workable link that opens my email. Whatever link that the user would type into cell D6 on the Setup Sheet would be opened.
I want to beable to click my object on "Link Menu" and have the macro open the link from cell D6 on the Setup sheet.
There used to be a way to open an Excel spreadsheet and have it go to a specific sheet....... I want to open Grants.xls and have it always go to the "index" sheet....
View 3 Replies View RelatedI have a batch script which runs the following line to open my excel sheet:
start m:exportad.xls
when this sheet opens it automatically refreshes from a csv file.
The problem is that I want this sheet to open, refresh and close. To close Excel I have been using the taskkill command in my batch script, but I have found that using this method doesn't update the sheet properly - the sheet only updates properly if I close Excel in the correct way, but this means manual input and I want this to be automatic.
How do I create a msgbox that tells me that the worksheet is currently open? I dont mean opening the worksheet and the msgbox popping up upon opening but I need the msgbox to show when the page is currently open?
View 9 Replies View Related