Need a way of quickly telling whther a user as a workbook open without trying to open the workbook yourself? I have many individual workbooks which I need to regularly update, before I can update them I have to check whether anybody else has them open and then wait until they are out before I can do the update.
I'm using a UserForm to inform the user that the program is still running. Unfortunately, when I do: UserForm1.Show the program hangs there until I hit the Ok button. How can I make the program run as the box is shown.
View 2 Replies View RelatedHow to display the userform when the workbook is opened?
View 9 Replies View RelatedI have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code. ex.
workbooks.open ....
if open
then msgbox " Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user....
I need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.
What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.
This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.
I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
View 4 Replies View RelatedI have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.
I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.
I'm sure there is a function someplace for this that can be used in a macro.
Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
I would like to know if I can set a formula or macro to say that if a value in a dropdown list equals, lets say "Business", then show this list of value in a dropdown list in this cell.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
View 5 Replies View RelatedI have attached a worksheet that shows the last and first name of an employee along with their hire date and term date, if termed. What I am looking for is a function that will tell me if a person was active as of 12/31/08 which will read true on the side of their name under active; I also need a function that will tell me if a person worked over 6 months in 2008.
The first person John Doe was hired in 2005 and has a true on the side of his name which meant that he was active as of 12/31/08 but as you can see Jane Doe has true on the side of her hire date and false on the side of her term date and I don't want it to read the first true but the last true of someones name, but Jane Doe qualifies for working over 6 months even though she started in 2007.
I have a 1000x1000 cell grid on one of the worksheets in my workbook where each cell contains one of the following four possibilities:
1) Blank
2) A string that is not defined in a lookup table (*lookup table = a named range on a different sheet)
3) A string that exactly matches an item in the lookup table
4) A long string that contains an item in the lookup table (which can be found with a FIND function, for example)
I would like to apply conditional formatting to my 1000x1000 grid, to color each cell according to the lookup table, where the last column has the name of a color that I can match with my conditional formatting setup.
I have solved everything except for No. 4).
2) is taken care of by using "Format only cells that contain" with "No Blanks"
My solution for 3) - using "Use a Formula to Determine Which Cells to Format" - uses
=VLOOKUP(A1,lookup_table,2,FALSE)="fill color name" So, for every different color I want to use, I have a different Formatting Rule in the Conditional Formatting dialog.
But 4) is tricky, because VLOOKUP can only return the result for a specific cell value.
If my cell contains France;Germany;Italy, I'd like the color to be according to what I set for "France" in my lookup table. Or, if my cell contains Germany;France;Italy, I'd like it to be set based on what I decided Germany is in the lookup table (i.e. "blue").
You can notice that one thing that is consistent is that I separate my "sub-strings" that are contained in the lookup table with a semi-colon.
So, one way might be to see if FIND finds a semi-colon, and then grab the string that goes before that character position.
I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
View 5 Replies View Relatedthis is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -
View 2 Replies View RelatedCreate a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
View 9 Replies View RelatedI did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
I Want make a code to open (OPEN File Dialog)
and it works good I can't change the Diroctory!
Sub OpenFile()
fileToOpen = Application. GetOpenFilename("Execl Files ( *.xls), *.xls")
ChDir "C:" ' Not work good
If fileToOpen <> False Then
Workbooks.Open Filename:=fileToOpen
Else
Exit Sub
End If
End Sub
check this code.
I have an Excel application in which I use the Workbook Open event to show a userform.
This works fine when Excel is not already open, but if another Excel workbook is already open, the Workbook Open event does not work.
If i have a userform: frmUpdateInfo
How do i have it auto open or at least be ready for use when going to a particular sheet in a workbook (Sheet name: "Matches")
Am working on a spreadsheet which has two sheets, one for the visual interface and another for the data, the data is displayed on a form, the form is opened by clicking on the cells.
Currently this is working for the top row of A5 to AA5 or 1-001 to 1-024, however when I try to include A8 to AA8 or 1-025 to 1-048 it fails.
See attached spread sheet for code
The problem parts are in;
Sheet1(Layout)
Private Sub Worksheet_BeforeDoubleClick.......
Form (Ports)
Private Sub UserForm_Activate()
How to include A8:AA8 and more when needed?
I am writing code that requires data from another workbook. Instead of hardcoding the directory and filename (which changes daily), how can I have the code prompt the user to do this for me?
I want to open the file with:
links updated
read-only
Also, is there a way to have an initial directory that appears so the user need not go through a dozen directories every time to get to the file?
I have code that prompts the user to open a specific excel file. However, with the following:
Filepath = "..."
name = Application.GetOpenFilename(FileFilter:="Excel Files *.xls* (*.xls*),",)
Workbooks.Open name
the default directory you start out in in the window that prompts the user to select a file is NOT ...
How can I properly assign which directory the user starts in so they need not go through 10 sub-directories everytime they run this code?
Assume that the workbook Book1.xls opens a user form named myUserForm.
I'm trying to show only the user form on the desktop when I open Book1.xls, with Book1.xls minimized on the Taskbar.
And , if the Cancel button on the form is clicked, then unload the form and close Book1.xls.
That simple!
In ThisWorkbook, I have the event:
Private Sub Workbook_Open()
ActiveWindow.WindowState = xlMinimized
ShowTheForm
End Sub
On the Form, I have the Cancel button:
Private Sub btnCancel_Click()
Unload Me
ThisWorkbook.Close True
End Sub
The above procedure shows the form, with Microsoft Excel blank window in the background, and produces a small bar at the bottom left of the screen for Book1.xls
Click the Cancel button, and both the Form and Book1.xls close, but the blank Excel window remain open !
I suppose I would not be able to edit this version of Book1.xls, but that is fine for now!
I have this script below which I am trying to modify to pull information from each file in the folder and draw cell values from different sheets within the file. I have the script working prior to the modification but the difference was I was pulling all the different cell values from the same single sheet in the file.
I believe the issue I have (Excel reporting that file is already open and then eventually erroring out) is because the way I'm going about this is causing Excel to open the file multiple times without closing (my coding isn't the best) here -
Do While fn ""
On Error GoTo Handler:
Set ws = Workbooks.Open(myDir & fn).Sheets(7)
Set ws2 = Workbooks.Open(myDir & fn).Sheets(2)
Set ws3 = Workbooks.Open(myDir & fn).Sheets(3)
Set ws4 = Workbooks.Open(myDir & fn).Sheets(8)
Sheet4.Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 6).Value = _
Array(ws4.Range("AI6").Value, ws.Range("ac4").Value, ws2.Range("ac4").Value, ws3.Range("ac4").Value, ws4.Range("ac4").Value, myDir & fn, fn & "-" & ws.Name)
Workbooks(fn).Close False
fn = Dir
Loop
I'm hoping there is a better way of pulling information from multiple sheets in the file that actually works as the above now I think it through is obviously not very clever.
A second issue I have is that whilst some people running this script don't have access to save to this network drive (so can not save changes), some users CAN. Is there a way I can force the script to open these files as Read Only or ensure that they are closed UN-saved - just to cover any mishaps??
Here is the full script -...................
We have several salespeople who maintain a workbook tracking their workflow. I want to compile the customer account number, account name and deal number for each rep to another workbook. A new sourcebook is started each week. This code is in the target book
'Option Explicit
Public sourcebook As Object 'the workflow workbook
Sub GETDATA()
Dim wsc As Double 'the number of pages in the workflow
Dim col As Long 'the column in this workbook for returning values
Dim IntEndRow As Double 'the end row in this book
Dim SrcEndRow As Double 'the end row in workflow
Dim findval 'the column workflow where the sourcedata is found
My code allows the user to select a workbook and then loops through each sheet in that book searching for the column which has the info I need, this changes on each sheet as the reps each deal with different types of customer as so take different details. I solved this by having column headers in the target book and searching for them in the sourcebook.
This code works when I step through it but falls over at the "Set sourcebook" line when I run it where it returns a breakpoint error.
I want to open a user defined file name in excel and then activate it , to run a macro on it.
View 3 Replies View RelatedThe macro works when I have my user name in the path but when I try to insert an environment it does not work.
View 3 Replies View RelatedI'm trying to write a macro that will bring up the "File Open" dialog box, and allow the user to navigate to the directory where the file is located. It won't be the same file every time, and the files will likely have different names.
View 14 Replies View RelatedThe code below opens a window which allows the user to select a file to open. Apparently, it starts in the directory of the file containing the macro. How would I modify this to open in "M:Archived PO ResponsesProcessed"?
View 3 Replies View Related