I have a file for which I wish only selected pages to be printed, so I have disabled the print function in the ThisWorkbook module. Instead, the user chooses from a menu of available sheets to print, via a user form, and then requests the print.
I've got most of this sussed in my mind, but the users need the option of which printer to use (loads of networked printers available and the choice will depend upon monochrome for draft print or full colour for final print), so I want to be able to call up the printer dialog box using
Application.Dialogs(xlDialogPrint).Show
BUT!!!!
I want all the options on the dialog box disabled, except for the choice of printer as clicking OK after choosing the printer adds the active sheet to the list of items to print.
Is there a Macro to automatically print the selected print area (print area is also established within the macro itself) to a user's default printer; whatever that printer may be? Note that this printer and/or printer name could change depending on the user.
I would like this to actually select and print to the default printer without Excel having to ask you. I don't even want the "OK" box to show up if possible. I just want them to run the macro, execute all it's duties, and print, all in 1 shot.
The below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.
Is there a way for the code to open the print window so I can change the printer and select number of copies?
Code:
Sub Print_Button() Dim ws As Worksheet, cell As Range Set ws = Sheets("main") Set cell = Range("g2000").End(xlUp) Do Until cell.Value "" Set cell = cell.Offset(-1, 0) Loop ws.PageSetup.PrintArea = ("A2:" & cell.Address) ws.PrintPreview End Sub
I have the following code that is going to trigger an export macro and an import macro allowing the user to upgrade the version of their report;
Sub Upgrade_SLA_Report()
x = MsgBox("Is the file you wish to export from open?" & vbCrLf & vbCrLf & "If it is click yes" & vbCrLf & "If its not click No" _ & vbCrLf & "If you need to abort the macro click cancel", vbYesNoCancel)
Select Case x Case 6 MsgBox "Please use the Windows menu in Excel to ensure that the file" _ & "you wish to export from is the ActiveWorkbook!", vbInformation ' Missing step If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub ExpWb = ActiveWorkbook.Name Case 7 On Error GoTo Handler .......................End Select
Problem is, I have a missing step within Case 6 which allows the user to ensure that the file they are exporting from is the ActiveWorkbook.
I want to force a user to select a value from a Combobox that I set up in a newly created worksheet from inside a macro - while the macro is still running. I have the code to create a new worksheet, size it accordingly, add a Combobox control, assign a range of values for the drop-down list, but do not know how to suspend the macro and force the user to make a selection from this Combobox before continuing. Not even sure if this can be done.
I'm trying to get a users' selected range to display in a cell as stored text.
therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7"
w/o the quotes.
I've attached an example workbook. Here's the formula thus far:
Sub SelectRangeBox() Dim rnBody As Range Dim vaMsg As Variant Do vaMsg = Application.InputBox( _ Prompt:="Please enter the message-text:", _ Title:="Message", _ Type:=2) Loop While vaMsg = ""................................
Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
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 a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code() Dim Rw As Long For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code() Dim Rw As Long Dim Rw2 As Long Dim Sourcesheet As String Dim Destinationsheet As String Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
******** ******************** ************************************************************************>Microsoft Excel - Rod Extract Test Master.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutCR1=CRCSCTCUCV1Loan NumberCollect Tax EscCollect Oth EscPay Int on EscRE Tax Monthly Const2304100830NNY$0.003304100830NNN$0.00Borrower,Master,ARM [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I need to have happen is that the user decides what duplicate row to delete. The reason that there is a duplicate is that Row 1 has something different from Row 2. In my example above, CU1 is different from CU2. This is what caused this loan number from CR to be duplicated. I need to delete at my discretion one of the 2 rows.
What I would like is to have the user Select Range CR1:CR2 (this is relative in the sheet), run the macro, have it select the 2 row range, compare (relative) B1:B2 are they equal, compare C1:C2, are they equal, etc through the rest of the range. I would like the screen to update and stop with the range highlighted that is and and a MsgBox to click ok to Continue, then continue through the rest of the range. Once it has finished cheking the range, can the user use the mouse to select the range from CR that needs to be deleted, have the appropriate range selected and then the MsgBox Is this the range you want to delete? Y/N........................
I have a protected worksheet with a mixture of locked and unlocked cells, and I'd like to provide an option to allow the user to select a large range of mixed (locked and unlocked) cells and, in the unlocked cells only, pre-populate with a standard formula.
I've got most of it working, but what I need is the specific code that allows the user to specify the range of cells they want the macro to act on.
I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?
I am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.
I have an event handler that runs a procedure when a user double clicks a cell. This procedure modifies the contents of this cell and other cells. I want the user to be able to select the cell so they can double click it, but I don't want them to be able to single click it and modify it by hand (accidentally or not). Is there a way to lock/unlock the contents of the cell without locking the cell itself?
In the attached file, I want to have the user select a receipt # and have it copy the information, for that receipt from the log, to the receipt tab. Then I want it to print the receipt. The macros that are in the file already, were created before I added a column for Receipt No., and don't ask for user input. File is attached.
The User has made multiple selections with the mouse. The spreadsheet is filtered. The user will usually make different multiple selections on the following columns: A and X through to AR (inclusive).
I just need some code to capture these various multiple selected ranges so that I can copy the selected range as shown below:
how do i print on a3 paper? i can't find the option anywhere...i can select user define size but then where do i define it?!? i know this is a silly questions
I am attempting to use an if statement to stop a user from trying to input a listbox as a null value. The if then statement is not working. The listbox value is null but the if statement doesn't react.
If listbox1.Value = Null Then MsgBox "you must select a serial number"
What I am looking to do is search using a user input and select all the rows that contain the entered search text.
I have 4 sheet workbook, columns are the same across them all, and what i require is to be able to take a text input from a user, maybe what column to search, and in what sheet. Then search the relevant sheet for matches. Once found the record that contains the match is to be copied to another sheet where filters can e applied. There will be multiple records selected for each search.
Code: Else Sheet8.Unprotect Password:="rm123abc" Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item" Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True End If
Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.
So I have two questions:
1. How can I enable allow user to select locked cells 2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?
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 was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run.
This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable.
What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there?
To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
I have a userform which allows user to selected a date formt he calendar. In my code I name the value selected myDate. I want to have a line of code at the begginning of my code that checks if myDate = a date already listed in column H on a sheet named "all dor's". if it does match then a msgbox appear saying "date already entered" and the code return to the opened userform.
I've created a user form (click button on 'main page' to display). Combo Box called 'Last name' gets populated with all second names from the sheet 'Staff'
When a user selects a second name from the drop down list, I would like excel to automatically update the list box 'First Name'. Also, If a user changes the first name or Last Name, this should be saved
I wanted to create a userform with multipage. On the first page I want to use a ComboBox that would allow the user to select the customer. The customer name is on the sheet that I named "Customer" which also contains the street address/city/state/zip. What I would like is, once a customer is selected, I want a textbox that will be populated with the correct address taken from the sheet named "customer".
On the second page I'd like to use it for Product Info. I want to have a ComboBox, Text Box, a Command Buttons. The ComboBox is to select the "Model#" from the sheet named Product Info" and the TextBox is for the user to write their comments. One of the Command Button is to be view a sheet named "Quote" and the other is to send the "Quote" through email.
