I have created a userform template and is working fine, the problem is the users are not able to minimize the template nor open any other excel files.
How to allow the template to open any other excel files or macro enabled files and also to minimize the windows.
Every time I open a new file in excel it opens up the file and the other file I have open disappears. Can I open a file so that there will be two separate windows?
View 3 Replies View RelatedI have been given a macro recorded on a mac and it works fine with Mac Excel 2011. Unfortunately, I need to make it to work on a windows machine, but it comes up with an error message "macro unable to set width property of the window class. Run-time error 1004". When I press debug it takes me to the line .Width = 1456.
Below is the chunk of the code that becomes highlighted when pressing debug.
With ActiveWindow
.Width = 1456
.Height = 795
End With
I use a code to work on all the sheets across all the files. But now I want the code work only on sheet named Paid across all the files.
Sub PP()
Dim wb As Workbook
Dim ws As Worksheet
Dim varWBnames As Variant
Dim varItem As Variant
varWBnames = Array("Book4.xls", "Book5.xls", "Book6.xls")
For Each varItem In varWBnames
Set wb = Workbooks(varItem)
For Each ws In wb.Worksheets
ws.Activate
ws.Range("A1").Formula = "=A2+A3"
Next ws
Next varItem
End Sub
What's the code for opening a Windows Explorer window from within an Excel Macro?
View 7 Replies View RelatedFirstly... using VBA i'd like to disable any save function to everyone other then me and a colleague on a spreadsheet (The one below).... Is it possible that just the two of us will have the ability to save and no other user?
Secondly... On opening one work book... in this case "Productivity Test" i'd like it to simultaneously open "Productivity Test Back End" both saved in P:MI TeamNew Prod with the back end remaining locked totally.
The reason behind this is these two spread sheet's are linked via sums, productivity test uses the back end as a kind of data source so i'd like "Productivity Test Back End" locked so they cannot see it/amend it..
Maybe i could use a bit of code that updates the link everytime something is used as opposed to having the back end open at all...? What do you think?
The last thing, i have alot of macros that run quickly on my PC, if my manager opens productivity test and uses the spread sheet it appears to run alot slower.
There are alot of tables in Productivity Test that are hidden and only appear when needed while everything else remains hidden. The example below is one buttons code..
ub Selectdsrtablenew()
'Select the correct table for users to view DSR detail'
Application.ScreenUpdating = False
Rows("23").Select
Selection.EntireRow.Hidden = False
Rows("26").Select
Selection.EntireRow.Hidden = False
Rows("36").Select
Selection.EntireRow.Hidden = True
Rows("47").Select
Is there any way of stream lining the code here to make it smoother as it appears quite a few times in the report for different buttons.. Maybe defining table names?
I am trying to do something which is very slow and manual. What i have is like a seperate spreadsheet from every day and have to save each one down manually in the respective date. so for example column C has the date in already. Can you tell me how to save all the excel workbooks which are open (if i open them all) and then use the date in column C (which is in the format 20090217) and save it as a xls in the format 170209 and then close the workbooks?
sorry this is a huge question, just takes me so long to save them all.
Is there a way to reference a cel in another file without having Excel ask you to "update your location" for each one?
View 9 Replies View RelatedI have tried searching for this code but just cant find (or remember) how i did it before.
I have a regular workbook (which changes name upon start up). In this workbook is a button that opens a linked database of products.
I want this button
1. to open the database C:/Database/db.xls
2. Minimize it
3. Maximize my regular workbook to its original state (this is where I am having the problem because the workbook name constantly changes)
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....
View 9 Replies View RelatedI have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
i'm having trouble with the following code. The first if statement works fine, it's on the second pass that get problems, i get a 'ERROR 13 type mismatch' on the Windows(NewFN).activate line and i cannot figure out why.
Private Sub cmdsave_Click()
Dim NewFN As String
Range("B5:G21").Select
Selection.Copy
If Module3.firstTime = "y" Then
NewFN = worksheets "Recalculate").Range "G5").Value
".xls"
Workbooks.Add
I've had a new machine, and for some reason if i try and open excel files by double-clicking on a .xls file, excel tries to open each part of the filename individually!!!!
For eg, if i have a file called week 1 report.xls, and i double click that, it gives me 3 messages saying:
cant find c:filepathweek.xls
cant find 1.xls
cant find report.xls
I can get round it by going into excel first, and opening the files from there but it's a right head-doer!!!
I'm on windows XP, and using excel 2003 to open the files...
The following 3 macros do the following.
1st macro . . . When the button is clicked for this macro, Windows Explorer is opened and the folder MyMenuFiles is selected. Works Fine.
2nd macro . . . When this button is clicked, the windows explorer opens with My Documents selected. Works Fine.
3rd macro . . . THIS IS THE PROBLEM ONE. When this button is clicked, windows explorer opens, with the C: drive selected. I want My Computer selected.
I use the below code to close ALL open Internet Explorer pages. Im trying to adapt this to close ALL Windows Explorer Folders.
Dim Shell As Object
Dim IE As Object
Dim i As Variant
Set Shell = CreateObject("Shell.Application")
i = Shell.Windows.Count
On Error Resume Next
Do While i > 0
i = i - 1
Set IE = Shell.Windows(i)
If TypeName(IE.Document) = "HTMLDocument" Then IE.Quit
Loop
I'm sure this is really easy, but I'm learning and not having luck in the search option for previous posts. Thanks for your patience in advance.
I want to open a windows explorer window by using a button in Excel.
I have a few macros that need to work with various worksheets which are in different windows.
1. Make the user name the files a certain way for consistency
2. Make the user type the name of each window at the start of execution
I was wondering if there was a more flexible option though: one which would allow the user to choose from a list of open windows (similar to the Window>More Windows>Activate function in Excel).
Ideally the macro would run and say something like, "Which of the following open windows contains dataset1?" "Which of the following open windows contains dataset2?"
I had this set up in Excel 2003, but our main shared drive just went down and after remapping this link didn't work. How do I do this in Excel 2007?
I want a folder to show up on the left hand side of the "Open' and 'Save As' windows under My computer, desktop, My Documents, etc.
I need minimize button on the excel form. How can i do this?
View 9 Replies View RelatedIs 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'm working on an Excel workbook that is opened by a batch file, updates information, copy and pastes that information to PowerPoint, and closes itself after spending two minutes idle. This is all for an employee communication monitor that runs 24/7. However, I don't like that the Excel spreadsheet displays on the monitors for two minutes every time it opens and runs.
Right now I was using the bit of code below to minimize the workbook when it was done copying and pasting the information. It works fine for the monitors, but the application is not visible in the Taskbar.
Sub Workbook_Open()
...
Application.Visible = False
End Sub
Is there anyway to minimize the workbook while keeping the window visible in the taskbar?
I am using Windows 7 and have a piece of code that brings up a warning message after 20 minutes to warn the user they have been in a workbook for too long.
If they have the Excel application window minimized, is there a way to get Excel to take the application out of minimized mode into max screen, before revealing the message.
Currently when the warning message activates, the excel icon just flashes in the start bar. Some users may not see this.
Here is the code I have so far:
[Code] ....
I have already tried simply adding a piece of code with maximize screen, however this doesn't seem to work.
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice.
How to fix it?
When I first open a workbook most of the functions don't work. for example the vertical scroll bar does not move. When I close the workbook and then reopen it for the second time it works fine.
Happens with any workbook I have.
how to get excel to open a work book in my c drive if i have given it the location in cell m2
eg: the cell is M2
C:AVERAGE PRICE (update 2009) 240809
i need the macro to open this workbook when i click the button in the workbook i have open that has the above in cell M2
I have this code...
Code:
Sub Macro1()
'
Sheets("Log").Select
Range("F1").Copy
For Each wb In Workbooks
If wb.Name Like "Test Book*" Then
wb.Activate
Sheets("Data").Select
Range("O2").Select
ActiveSheet.Paste
[code].....
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
I'm trying to get a Workbook.Open macro to work. I"m using the exact name for the location. For example:
Workbooks.Open ("O:AdministrationPM Weekly's2007PMWeeklyWE")
My other locations are working fine. For example I am using:
Workbooks.Open ("O:AdministrationDCS Week EndingStats"
I currently use a spreadsheet to track my work and I would like help automating some of the functions. I don't want to use macros if at all possible. I have some formulas but it doesn't do enough.
what i do is audit other peoples work and send out error notifications when needed. i use the spreadsheet to help me tack the status of the error notifications. when I send out an error notification the auditee has up to 3 chances to rebut the error so i need to keep track of where the follow up lies and when the error is finally accepted or removed (process completed).
I would like column U to tell me where we are in the process...
1.notification sent (by the auditor)
2.reply received (either accepted or 1st level removal request by the auditee)
3a. if error accepted- process complete
3b. if removal request-notifcation sent(by the auditor - error removed or error remains)
4.if still an error(following 3b) - reply received (either accepted or 2nd level removal request by the auditee)
5a. if error accepted- process complete
5b. notification sent (by the 2nd level reviewer - either error removed or error remains)
6.if still an error(following 5b) - reply received (either accepted or 3rd level removal request by the auditee)
7a. if error accepted- process complete
7b. notification sent (by the 3rd level reviewer - either error removed or error remains) & process complete (3rd reviewer decision is final)
I would like the V column to be the "date of last action"
since the notification bounces between people, i need to know who is currently responsible for followup & how long they've been sitting on the work. i would like the date to populate based on the manually inputed fields where I input sent & received dates for each level of action.
the spreadsheet is too wide for the post so i will put it on a website...
http://public.box.net/kingerick89367
the columns are all titled and all manually inputed except for column U which is the one that I want to autopopulate with the current status of the error notification. the formula is a IF statement but due to the 7 formula limitation It came out too simple. it's better than nothing but I want it to be more specific.
Problem: I have textbox entries that are part of a Userform that opens using a macro on a speadsheet button. Once this form is open, I can no longer actively work in the spreadsheets.
Need: I need a way to minimize or "put on hold" the Userform so that I can freely move around in the spreadsheet. This could be in the form of a button on the Userform. Then, I need a way to bring back this userform to the point I was at before being put on hold so that I can continue to input entries into the textboxes (again, a buttton that could bring it back). There should be a way to toggle between both worlds.
I'm currently running MS Excel 2003 on Windows XP SP 3. When I launch Excel from the start menu, it starts right up. When double-clicking an Excel file, it takes approximately 45 seconds to a minute to launch and open the file. This happens no matter what the file size.
View 1 Replies View Related