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.
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.
I've created one file, that acts as a launching file. It does several things, including environment validation, and downloading new files from a web repository, etc... Once it has done everything it needs to do, it launches a 2nd file (the main user input file - which is tied to an external DB), fires the start-up routine in that 2nd file, then quietly gets out of the way.
This all works perfectly, unless the 2nd file gets closed before the start-up routine is finished. (this could happen for several reasons, including invalid login credentials). I've oversimplified the issue below: ...
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.
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.
In the following code, What do I need to add to allow the selected file to open when I click on it? I have a drop down menu that lists my .xls files.....as of now when I choose a file nothing opens.
Sub Auto_Close() On Error Resume Next Application. CommandBars("Patient List").Delete On Error Goto 0 End Sub
'================================
Sub Auto_Open()
Dim cb As CommandBar Dim ctrl As CommandBarControl
On Error Resume Next Application.CommandBars("Patient List").Delete On Error Goto 0
I have written a macro to loop through files and subfolders to open a specific file for a specific month. I extract the datasets for every excel file into a master template. The code works flawlessly when all of the files have the .xlsm extension. However, I need to open .xls file formats also. When my code opens the first .xls file, named "Staffing Plan.xls" it opens it and automatically makes it "Staffing Plan1 [Compatibility Mode]. I can't open the file in the original "Staffing Plan.xls" file format to read data from. How can I fix this?
I have the following code saving a copy of a spreadsheet as a backup every 30 minutes. The problem is the save code is running as soon as the file is opened. I want to save only after the file has been open for 30 minutes. The way it is now if I open the sheet up and something is wron the backup has already been overwritten by the messed up sheet. Will someone please let me know what needs changed to eliminate the inital running of the code?
Sub do_something() sec = 1800 when = Now + sec / 60 / 60 / 24 Application.OnTime when, "do_something" Application.DisplayAlerts = False ThisWorkbook.SaveCopyAs "S:QUALITYTest Results Spread SheetBackup of Riser MezRiser Mez backup.xls" Application.DisplayAlerts = True End Sub
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?
A client has sent me an Excel workbook that appears on the screen with the message adivising that it is Protected View as a result of having been downloaded from the internet. If I click on the Enable Editing button the workbook is completely deactivated. I have played around with my Trust Centre settings but nothing seems to work.
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?
Is it possible to set the default page to open when clicking the file button? In particular, I use the recents feature a lot but never have I looked at the information on the information page. Everytime I click the file button I have to then select recents which is annoying.
I need to randomize cell ranges A2:F51 across the rows. I don't want data to be randomized in any other way then across rows. Is this possible to do. I have found many apps that make this possible, however I don't know how to incorporate this function into my macro.
I'm running a Secret Santa DJ competition this year and today now wish to make the draw.
Basically all entrants send in their mixes (in this case - upload to a webserver) and I then wish to try and use Excel to make the draw.
What I'd like to do is have a list of all the entrants in column a, use RAND() in column b to assign them each a number, and in column c use the list of entrants in column a to assign each person a mix to receive.
Through my browsing and searching on here, I came across the following (entered as an array); it's brilliant but unfortunately gives me duplications.
I have an Excel table with 7 columns. I have used a formula to randomize the data in each column, and it works fine, accept that it resorts/re-randomizes the contents of the table every time data is entered anywhere in the workbook. I would like the data to be sorted/randomized only once, and then only again upon pressing F9, since I have other spreadsheet which reference the data in the table.
This is the formula I use to sort/randomize the data, and I am using Excel 2010 on a PC.
=VLOOKUP(SMALL($C$2:$C$20,B2),($C$2:D20),2,FALSE)
Column B = numbered 1 - 20 Column C = =RAND() Column D = last of names Column E = sorted/randomized list of names.
Suppose in a work book I have a list for data in 5 rows.
1 2 3 4 5
Is there a way to randomize the data with one created button click (like hyperlink button used for linking) so that it may show any random output like 2,1,4,3,5 etc. Next time another click on randomize button creates another set of totaly random order of the 5 data sets.
I have a table like the one below, only it has a few hundreds of rows and columns and I need a solution in order to fill in the blank spaces with 0 and 1 in order to get the total by row and by column. Is there any way to do this with a formula/macro ?
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
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!
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.
