The example file gives a better visual explanation. Just want to copy a range of cells by pressing a command button. However I want the macro behind it to ask the user which set of rows to copy.
View 14 RepliesInputBox User Validation. Need to modify my code as follows?
View 5 Replies View RelatedI have not used Inputboxes or Userforms much. I have read through some of the messages here, but I need to learn more about them. I need to create some type of user interface, where the user would be asked to select between two choices. Based on the choice made, a macro would be executed. The two choices run different macros.
I need direction or an example of an Inputbox that shows two choices instead of a blank input space.
I have found a base macro that has most of what I want the only problem is I need it to search column A by the data entered by the user in the inputbox and send out an email if it meets all the requirements. I also need it to be able to be able to do that search by only the first letter of the company name (example, only needing to put in A in the inputbox for Amber Inc.). If I remove the red text the macro works perfectly but just runs down the list on the spreadsheet.
Column A has company names, Column B has email address' and Column C has the company contacts first name.
[Code] .....
i need an inputbox to appear asking the user to enter an amount(we'll call XX).
Now the code will find that value in Column F, and delete the row that contains the value XX in F
I am using an inputbox to get a range a data from the user.
View 10 Replies View RelatedI need to force users to enter one of these words only (Year End) or (Qrt) using inputbox method.
Note that user must not leave the inputbox until he/she enter one of these words.
I am trying to create an inputbox using code from this site:
Sub NumericDataType()
Dim lNum As Long
On Error Resume Next
Application.DisplayAlerts = False
lNum = Application.InputBox _
(Prompt:="Please enter you age.", _
Title:="HOW OLD ARE YOU", Type:=1)
On Error Goto 0
Application.DisplayAlerts = True
If lNum = 0 Then
Exit Sub
Else
MsgBox "You are " & lNum & " years old."
End If
End Sub
I get an error on the first line that says, "Compile error: argument not optional".
I have a store report, which every now and again needs to have a new line added for a new store that has been opened. I need a inputbox to display so that you enter a "store number". Once you enter the store number, the macro adds a new line to add the store number in numberical order.
Sub AddRows()
Dim r As Long
For r = Cells(Rows.Count, 6).End(xlUp).Row To 2 Step -1
If Cells(r, 6) <> Cells(r - 1, 6) Then Rows(r).Insert Shift:=xlDown
Next r
End Sub
but find it somewhat unflexible. I don't always use the same column reference to determine where to add the rows. I was hoping someone would be able to integrate the inputbox usage that is in this delete rows code I got here
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)
I am wanting to run an insert row macro and it prompt me asking what column I want to reference. Whenever there is a change in that column, insert a row.
I wanted a macro to insert (the number of rows from Input box) below the active cell. Searching the forums, I found the following code.
Sub InsertRows()
Dim Rng
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(1 0), ActiveCell.Offset(Rng, 0)).Select
Selection.EntireRow.Insert
End Sub
If I enter a number it works fine, but if you click on the cancel button or the X to close the Input box, I get a Run-time error (13: Type mismatch). If you enter zero, it inserts 2 rows above the active cell.
I tried to modify another macro, I found on the site, that inserts rows and copies the row above, but always get the Message Box "You didn't specify a range!"
Sub InsertNumRows()
'
Dim Rng As Range
Application.DisplayAlerts = False
On Error Resume Next
Rng = InputBox("Enter number of rows required.")
On Error Goto 0
Application.DisplayAlerts = True.............................
I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
I am using below code to copy certain data(Range("C4:R46")) and paste after certain row(After 43 rows) with 50 times
I want to popup a inputbox who asked for how many time u want to paste data, if i choose 4 then paste data after certain row(After 43 rows) with 4 times
If I choose 14 then paste data after certain row(After 43 rows) with 14 times
[Code] .....
I have some code that does some maintenance on my workbook, calls a second macro to delete all of the VBA and saves it with a new name.
However, after it finishes and I try to exit Excel, I am still prompted to save the workbook. I'm not sure why because the last thing my code does is save the workbook and no additional changes have been made.
Code:
'Loop through all cells
For Each Sht In ThisWorkbook.Worksheets
With Sht
[Code].....
I am copying fomulas from one row to the next. Then I am using the Find and Replace tool to replace some key text. This technique has worked 65 times. Now when I try to Replace All a box comes up asking me to Update Values for the text im replacing. Why is this happening?
View 4 Replies View RelatedI'm doing a Vlookup and when I copy down the formula Excel asks me to update the values by opening a new document. But this I can't or don't want to do - but I can't get rid of the box that asks me update the values with a new file.
How to remove this?
I have a spreadsheet with the following headings:
A
Claims Number
B
Name
C
Scheme
D
Admin
E
Date
I need to create a user form that will "pop up" and ask the administrator to enter the above details.
A - should be created automatically (ie last claim number + 1)
B - user enters manually
C - data retrieved from a list
D - data retrieved from a list
E - date is the date the information is entered
I'm trying to print out multiple excel sheets in which it asks me if I want to save the changes or not every time. I have macro settings set to low so I always accept the macros, if I don't have them set to low I'm always asked the question of whether or not I want to run the macro for over 20+ files. Anyone ever had similar problems when printing multiple files?
View 9 Replies View RelatedWhen selecting multiple excel files in explorer, you can right click and choose to print the selected documents. All documents gets opened and printed but the files stay open and you have to manually click on YES or NO to save the changes.
Is there a way not to see this window appear. So documents get opened and printed and closes itself without asking anything. Maybe it can be done with an option in the preferences. I'm not sure.
By the way the documents where created with excel vba just in case it's relevant. Also the version of Excel is an older version, I think it was Excel XP or 2002/2003.
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
I am trying to create a function using the functions of IF and "And", but for any reason I don't know how to add the Add function
My function written in excel will looks like =IF(AND(A80="00",C80<>"8300"),"yes","no")
LOB Cost Center
00 1000
00 2000
00 8300
01 5000
02 8300
Function Allocation(LOB As Integer, CostCenter as Integer) As String
If LOB = 00 And CostCenter <> 8300 Then
Allocation = "Yes"
ElseIf LOB = 1, 2, 3, 4, 5 Then
Allocation = "No"
End If
End Function
I have a macro that's running quite well and is cross-posted here:
[url]
What I need to do is allow for user input. I want to create a dialog box that pops up and asks for an input range, when the user runs the macro. Values can only be from 1 - 50. And the user won't be allowed to select broken sequences, like 6, 9, 12-15.
They would have to be continuous, i.e. 6 - 15, etc. I'm still very new to VBA. I can play with the control toolbox, but I have very little understanding of how to interface with the components that I'm adding/creating. Once I have that range, I want to use the inputs as the 'start' and 'stop' points for my loop. So I need to capture the values and pass them as input parameters, I'm guessing.
I can modify the loop to accept the inputs, I'm just not sure how to capture them from a user input dialog box.
I have a mental block when it comes to writing functions.
In a cell I have the Post Code followed by a "#" sign (no speech marks) and sometimes more text
e.g. FY6 8JF#ABCD (where the Post Code is FY6 8JF)
The "#" sign always indicates where the Post Code ends, i.e. I want to return all the characters to the left of the "#" sign.
In a spreadsheet I would accomplish this with "=LEFT(AA14,FIND("#",AA14,1)-1)"
where the string of interest is in cell "AA14"
How do I write a UDF to do this?
I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:
[Code] .........
You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.
FC_Macro_Sample.xlsm
So I've got Sheet 1 with say
____A___B___C
1___m___i___c
2___r___o___s
3___o___f___t
I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet
and I need this to create a new sheet and do that everytime the button is pressed.....
I wish to create a button which will allow the user to goto the page in my spreadsheet.
I no i can use macros or hyperlinks to do but i can only use this button once for it to work. I want to cut and paste the button to save time.
I want to create a user input for a "LARGE" function so that I can then return the specified # of "large" items. i.e. user wants to get the top X number of users, user then enters number either into a dialog box or cell, code then looks at the number and populates a range of cells based on the input value. The current code that I am using simply refers to an existing table (r9:r30) that I then used the auto fill to copy to cells below it.
=INDEX(Sheet2!B$20:B$961,MATCH((LARGE(Sheet2!$F$20:$F$961,$R9)),Sheet2!$F$20:$F$961,0))
I really want to be able to just get a user dialog box going that will automatically populate both the range r9:rx (where X is the user input) and then also copy the above formula into the x number of cells below it.
I have a workbook with 23 pages, each running a macro to calculate the final "answer".
I would like to make the title page comman button create a dialog box, listing the 23 pages/macros with a check-box for each (default is "checked") asking the user which macros / calculations to execute. Any ideas how to do this?
I am trying to avoid making this a sheet in my workbook.
Is it possible to use vba coding to create a pop up user form by clicking on a button in excel?
I want to create a pop up form that a user can fill out with fields for name and address information. Then click a button to save and close the form based on the last name field box.
I am working on a spreadsheet and would like to create a cell that allows the user to input a number (1 to 5) which will then equate to a percentage(%). I would then like this to deduct that percentage from a total number...
To try and explain this.. Should the user input 1 into the cell, this would then equate to 3% - this would then deduct from a total that percentage. Input 2 = 5% etc..