when a cell in column "K" is clicked, an input box gives the user the choice of entering a number between 1 and 12. That value then inserts row(s) based on input value ..
View 6 RepliesI have managed to write a macro that almost does what I require, but it inserts the copied row at the end and I want it to insert before the row it's just copied. Any ideas how I can modify the code to do this?
I have an excel sheet that I've automated for my shop ... Basically it copies a set of rows from one sheet, asks for which cell you want to start the paste at, then pastes it... Here's what I have so far:
[Code] .....
So I tried to use a variation of strReturn to indicate number of rows wanted but that didn't work ... Basically I'm going to create the "data" to include 50 rows, but have it selectable for the user to say they only want 5, 7 or 29 rows ...
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
I am designing a from with a variable number of titles. The user should be able to insert a title into a column by typing in the title that he wants into a text box, and pressing a command button that I have created ( named add). The problem arises when I try to get the text to lie vertically as opposed to horizontally (in the cell, not as a text box. If this can only be done as a textbox, let me know). Is there any way to do this?
View 5 Replies View RelatedI am trying to give a cell a unique ID based on the contents of 2 other cells.
Basically in cell A1 a value is entered between 1 and 13 and in cell A2 another value is entered between 1 and 4 to find the unique ID that matches these I need to look in DF4:FE4 to find the number that matches A1 ( there will be 4 matches) then look in thoses 4 matches in DF5:FE5 to find the match to A2 ( there will only be 1 match ) then return the id from the relvant cell in DF3:FE3.
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)
View 4 Replies View RelatedThis code works perfectly, assuming I hand enter in the Input Box the sheet name in the correct format 10-08-08 ... etc. I would like to simply be able to click a sheet tab with my mouse instead. However, the hour glass anywhere outside of the input box prevents any mouse action.
It has something to do with string, range or variant. I've tried to set all "ShtName" variables to that and it generates errors.
How can I change this code so it sill accomplishes the same thing, but the input box allows the user to choose the sheet name with their mouse instead of having to hand type it in? This will prevent human error.
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I'm wondering if it is at all possible to have a cell comment automatically update depending on what data is input in the cell (via user input, VLOOKUP, etc.)?
For example, if cell A1 contains the text "CHARLES" with a comment saying "Employee of the Month", and is then subsequently updated with the new text "JOHN" (again, via direct input, a VLOOKUP, data validation, etc.) is there a way to have the comment automatically update to say something else, such as "Team Lead" for example?
I've considered using VBA to accomplish my goal, but am unsure how to compose an effective code to do so. I've also considered perhaps creating a named table filled with all the different comment possibilities I would like to have used in this cell and then inputting a formula in either cell A1, or the comment contained therein, that would then call the corresponding text from that table based on the data in cell A1. Honestly, I'm not sure that what I'm trying to do is even possible;
So here is my code
Code:
Sub Worksheet_SelectionChange(ByVal Target As Range) 'single click version
Range("J3:J32").Interior.Color = RGB(200, 160, 35)
Range("K3:K3").Interior.Color = RGB(200, 160, 35)
If Intersect(Target, Range("J3")) Or Intersect(Target, Range("J4")) Is Nothing Then Exit Sub
ActiveCell.Copy
End Sub
The If statement line gives me a 91 error. Simply right now i'm trying to allocate several squares in the sheet to be cells that when clicked will copy to your clipboard automatically. I can do 1 range easy but now i need to make it a little more advanced.
If we mouse click any cell (right/left) it put √ symbol in that cell... How can I do it?
View 2 Replies View RelatedI'm trying to have excel insert a timestamp on a specific range of cells. I have it currently working on one cell, but can for some reason am having trouble getting it to work on the entire range... Here is what I have currently...
PHP Code:Â
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$E$23" Then
Target.Value = Time
End If
End SubÂ
I would like this to work for the range E23:E28 and F23:F28 I'm sure its something simple that I'm not doing...
When i right click in a cell i have all of a sudden lost the option to Insert Comment.
View 6 Replies View RelatedI wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI am using data validation to restrict the number of Characters a user can input in a range of cells. The number of characters in this however can vary based on which selection they selected in a different cell.
To put this as an example, if the user selects "hello" in cell a1, the data validation would allow 10 letters in the cell range c1:c20. If the person selects "goodbye" in cell a1, then the validation would only allow 7 characters in cell range c1:c20. I have been using the worksheet Change function, however it updates the validation regardless of what cell is changed.
I am working in both 2003 and 2007 Excel.
This is a simplified version of my worksheet:
I have two cells, A2 and B2. When I enter a numeric value into B2, I want it only allowed when certain text values are in A2. I.E.: Allow any value in B2, but only if A1 is either "SYDN or "ADEL".
Now using info found in an old post, I can achieve this with a combination of data validation and code:
=OR(A2="SYDN",A2="ADEL") for the validation and for the code:
[Code] ......
The only problem is I also need to reset B2 if A2 changes to invalid data as a result of some other input to that cell (A2). In other words if B2 is showing "1" already and A2 were to change from "SYDN" to say "LOND", then B2 needs to be cleared. If A2 changed to "ADEL" then it doesn't need to clear but it is ok if it does because it is fine to re-enter "1" into B2 again.
I have attempted to attach a sample worksheet : Xl0000003.xls‎
Macro Based on an input cell’s (e.g. B12) setting of “yes” or “no”, format several other cells (e.g. B20:G20, B23:F23, C26:J26) as follows: keep the existing yellow shading if input cell is “yes”, change to grey shading if input cell is “no”. I would like this macro to run automatically whenever the input cell value is changed.
View 2 Replies View RelatedI'm trying to work out the way of having a cell give a result based on what has been input into two other cells.
Basically I'm doing up a risk management spreadsheet. I need to have in one cell the LIKELIHOOD of an incident, in the second cell, the CONSEQUENCES of it happening and ending up with a RISK MANAGEMENT ASSESSMENT in the third cell.
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example
If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
I have two coloumns on a worksheet. In the first column I have data validation drop down list. I want to be able to: if the first column "Other" is chosen then the next cell will allow text for description, if anything other is chosen in the first drop down list cell then I want the next ( text for descritption ) cell to be resticted.
View 4 Replies View RelatedCell A5 has a Yes_No pull-down list for data validation. In cell B5, I want to display the value 0.002 only if A5 is "Yes". If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.
View 4 Replies View RelatedI'm working on a sheet that gets filled in with background colors (red, light blue, and green) and I'm trying to come up with a way to automate the process of inputting the text in the red and blue fields (red always gets PROCESS and light blue always gets SHPD) but they end up in different locations in the same columns. is there a function or macro to make this happen? I have constructed a macro to compile the sheet from my online database as it is a live system.
MODELACTIONxxx1xxx2xxx3xxx4xxx5xxx6xxx7xxx8xxx9xxx10xxx11xxx12xxx13xxx14xxx15xxx16xxx17 This is a simplified version of what I'm working with, and i'm on Excel 2000
I want to track winning football teams by highlighting the victor. So lets say I have a cell that contains
Raiders @ Niners
and another cell containing "home" or "away" indicating the winner.
Based on the home/away cell, I want to either highlight Niners/Raiders.
Two formulas which I need in Cells E74 and E75.
Basically the formulas will depend whether the entry in D73 are Yes or No
in cell E74, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF ‘D73’ = ‘Yes’ then E74 = E70
in cell E75, IF D73 = ‘No’ then E74 = D74 + E70 ELSE IF D73 = ‘Yes’ then E74 = E70
Excel version 2010
Instructions are in the attached excel as well. Please see attached
If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.
=Column B>Column C, then turn input cells in Column A Red.
First four highlighted are examples of thise formula scenario
** All based off an input into column
How to use conditional formatting to do so???
Excel Conditional Formatting 06.24.14.xlsx‎
I do data entry for a driveway company. I get addresses and phone numbers, and then call the people to offer services. I keep track of my calls on the spreadsheet, color coded for my results, green for leads, yellow for voicemail/no answer, and red for not interested.
I have formulas in cells M2 through M8 to give me my overall stats for the calls I have made, it filters them by color for leads, missed calls, and rejections, and a couple other stats (mostly for my own curiosity)
What I'm trying to add is the cells L10 to M13, I want to be able to input a date, for instance 8/15, and have it run the same stats, just for that day essentially. So, I need it to search the last 2 columns in the table for the text that I input into cell M10, and then run the color based formula on those cells, and total them up in the appropriate cells, M11, M12, and M13.
The 2 Stats tables are the same at the moment in the sample, as I only included one particular street that I have mapped, my actual table is nearly 3000 rows.
Various Numbers Will Be in a drop-down List eg:
6718
0820
7141
0821
I need it to Be able to, When I Select '6718' from the drop down Menu
"375 CAN 2lB18 C/F D/COKE" will Appear in the Next Cell.
i was wondering if it was possible to move database entries from sheet to sheet using a user form ? any examples for this ?
View 1 Replies View RelatedI have a table of data which I would like to filter based on a manual cell input instead of the autofilter option.
The table has tickers in column B and has information from column B to U. I would like to filter the data based on a manual ticker that I enter.