Stop Input Into Worksheet Based On Yes No Response
I am trying to work out how to stop input into a document if a certain response is selected from a form in excel 2007. Basically, i need some programming so if a question is answered a certain way - the user cannot continue completing the form as it becomes "locked" and an error box comes up saying this is the case - and what action needs to occur. As the list is a data validation list - i cant just separate it out - so that if one response is chosen over another then the error message appears.
For example the question might be "Will you use contractors". If the answer is Yes, the cell turns red and an auto response fills the cell (VBA has already been written into the document for this to occur). What i need additionally is an error box to come up saying "This audit cannot continue as a Minor Plan needs to be completed" and the rest of the questions responses say something to the same effect.
View Complete Thread with Replies
Related Forum Messages:
Trying To Prepare For Every Possible Input Box Response
I have an input box that should only have numeric input. I have arranged for it to start loop back to the beginning of the box if someone enters letters instead of number, simply hits enter instead of making an entry, exiting on cancel. Now I need to have it loop if someone hits the space bar one time or more. Here is what I have:
Delta: On Error GoTo ErrHandler1 Response = Application.InputBox(Prompt:=MyPrompt, Default:=MyPrompt2, _ Title:=MyTitle) If Response = 0 Then GoTo Delta Z = Response If Response = Cancel Then MsgBox ("You have chosen to exit the macro. Shutting down.") Exit Sub End If If Response = False Then GoTo Delta If Response = 0 Then GoTo Delta
Stop Deleting Formula Cells Without Worksheet Protection
I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
Code To Stop A Worksheet Selection Change Macro Running
I've got a worksheet_selectionchange macro on a sheet, and another macro that you can run after it. The issue is that when the second macro runs, it also runs the selectionchange macro, and wipes some of the info that the second macro should be copying.
Is there a piece of code that I can use in the second macro to block the selectionchance code from running until it's compelte?
Automatically Add Data To Separate Worksheet & Stop Duplicates
Need to write the VBA code required to automatically add the names of the buyers & sellers as the auction info is entered on the Auction worksheet to the Transactions worksheet of the same workbook? Any member name should only appear once as this will be the list whereby we will generate the receipts by Location order when auction is finished.
Start/Stop Loop Based On Condition/Criteria
I have a piece of code that put a check in all checkbox in column B from row 5 to row 50 but in column C, I have data from row 5 to 38. I want the macro to stop at row 38 in column B. When the cell in column C is empty stop putting checks in column B. How can I make this macro Check all checkbox down column B and stop when column C is empty. How do I add a loop to stop when the cell in column C is empty?
Private Sub CommandButton1_Click()
Dim CB As Variant
For Each CB In ActiveSheet.CheckBoxes
CB.Value = False
Fill Number Series Based On Start / Stop Of 2 Cells
I have a excel sheet of data numbers which blongs a packet of inventory where i found number series like this:
Start Range End Range Qty
2101200 2101499 300
I have to draw this onto a series like 2101200 then 2101201 then 2010202 etc.
but some time these numbers are in qty 30000 or more. I have questions
1- If any macro colud fill series suppose if write number in A2 and qty in B2 so it reads the qty of b2 and fill the series in A. If i write 1 in A2 and write 50 in B2 so the series should be auto fill 1 to 50.
My Second question is opposit of my above question.
2- if i have different number series in column a and i want them to be as start number in column b and end number in column c and qty in column d based like mentioned below example.
Series [b]Start Range End Range Qty
1 1 1 1
3 3 3 1
5 5 7 3
Send Data From Input Box To Another Worksheet
I have created a form for my work. And I have input boxes for users to add data that I would like to get stored in another worksheet. What is the vba code to send data for an input box to another worksheet at the bottommost empty row; first column.
Private Sub cmdAddData_Click()
' Collect user name, find the space between first and last names, and separate the names.
UserName = InputBox("Enter the Program Name.", "Program Name")
spaceLoc = InStr(1, UserName, " ")
firstName = Left(UserName, spaceLoc - 1)
Using A Cell On Another Worksheet As Input For Data Validation
Situation: I have an Excel file with multiple worksheets. Each of these worksheets contain the same rules for data validation in a certain column (G).
Problem: If I want to add an item to the data validation lists I have to edit every single worksheet. I've tried making a seperate worksheet containing data validation items and using that as the source for the data validation rule, but Excel doesn't allow me to switch worksheets while defining the source.
In other words, I want to manage my data validation rules of multiple columns in multiple worksheets in one central location.
Convert The Data By Input And Categorize Into Month In Another Worksheet
am currently a QE using the following data for my report generation:
Date - Week - #Inspected - #Failed
Want to pull data into ANOTHER WORKSHEET from this one wherein the format will be :
Month - #Inspected - #Failed
Indirectly want Excel to directly convert the data by input and categorize into Month in another worksheet (will be linked and would be great if according to the date it could calculate the week as well)...so if somebody inputs defects by date it should automatically cumulate into respective month.
I'm trying to find a formula I can use to figure response time. The working hours are 7:00 AM to 3:00 PM and I do not want to count the hours between 3:00 pm and 7:00 AM of the next day.
Cell A1 has the call in DATE 12/06/07 and cell B1 has the call in time of 10:00 AM.
Cell C1 has the response date of 12/07/07 and cell D1 has the time of 9:00 AM.
So what I need is a formula in cell E1 that would count from 10:00AM untill 3:00 PM on 12/6/07 then pickup the count on 12/07/07 at 7:00 AM and go untill 9:00 AM.
So in this Example the final response time would be 7 hours or 7:00.
Select Only One Response
Trying to accomplish a bit of data validation...
A1 B1 C1 D1
The only acceptable input for these cells in a 'x'
If I place an 'x' in any one of the four cells than the other three must be blank.
Required Input Based On Validation
I have a form that when users hit submit, the data they entered gets transfered to Access (thanks to our MVP SydneyGeek for providing me with a macro!) I want to be able to limit or control the users who sends data, by making sure they enter every information that is needed.
I have a column that has a list using Validation. How do I make certain columns required based on what they choose on the list.. if they don't fill in those columns, I don't want them to be able to click Submit, showing an error that would say "Missing Required Fields".
Web Based Input Output
I got a question where I cannot think of a good descriptive title for, I have seen this done but do not know how it is done. An internet user inputs data onto a web form, and and presses a submit button. The submitted input is 'processed' on a through excel and outputs the result back the internet user.
1 - what is a good title for this?
2 - how is it done?
IF Formula Response Y Or N In Column
I would like a formula which will give a Y or N response in Column C.
If there is content in Column A or B the result should be Y.
If Columns A or B are empty the result should be N.
Column A = 2
Column B = 0
Column C = Y
Column = 0
Column = 0
Column = N
DropDown List Response Before Next If
Trying to Have a User Pick A response from the first drop down list before the second is activated and then after they choose from that list have an error message appear if the vlookup formula returns a #N/A. Here is the code in it's entirety the problem area is in the Elseif Pipe category Right now it doesn't bug but it also doesn't wait for a choice to be made it just spits out all the messages
Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim Add As String, Size As String, Sch As String
With Range("c11:c52"). Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
Determine MsgBox Response
Private Sub CommandButton1_Click()
Dim hojaRES As Worksheet
Dim howMuch As Long
Set hojaRES = ThisWorkbook.Worksheets("Resultado")
howMuch = 0
If total.Value = "" Then
MsgBox "¿Are u sure u want a cero?", vbYesNo, "the total value is 0"
I need to set the value of howMuch to 0 if one selects YES and ask for a new entry if one selects no. How can I specify the behaviour in depending on what the user decides?
Incremental Numbering Based Upon User Input
I have created a macro that processes through information for the creation of checks.
I would like to have a way for the user to input a check number, and Excel to take that number, drop it into cell A2, then increment it and drop the next number in A3, and so on until all of the checks have been numbered. The process should stop at such a point as there is no more text in column B.
Here is an example. Let's say I have 35 checks to write. The first check number is 200. I would want a user box to pop up which asks me for the first check number. I would enter 200. Then the process would place the number 200 in cell A2 (A1 is part of the header information for the checks.) In cell A3, the number 201 would be placed. The final check number would drop into cell A36. No check numbers would appear in cells A37 and greater because cell B37 is empty, signifying that there is no further check data.
Delete Rows Based On User Input
1) Allow user input of a number between 1 and 999
2) Search column three and delete any rows that don't match this number.
I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
Updating Value In Table Based On User Input
I have one sheet to act as a splash page for user input. The second sheet stores all relevant data. My goal is for the user to input a numerical value in a cell (or input box) and then click a submit button. The code for the macro should look at a cell on sheet 1 which displays the primary key of the row in the table on sheet
2. Based on that primary key, it should look to sheet 2 and then insert the value into the table in the correct row and column. The column headings are the days of the week, and the specific day the user is concerned with is also displayed on sheet 1.
A1 = Day of the week
A2 = Unique primary key
A3 = user input value
Rows=primary key (001 - 999)
Columns = days of the week (Monday - Friday)
user inputs '5' on sheet 1 with 'Tuesday' and '007' selected in their respective cells. I would then like 5 to be copied to B7 (row 7 for 007 and column B for Tuesday).
How Do I Autofill Colors, Based On Input Of Text
i'm trying to do is have a formula that when certain text is typed into column 'b' (fmc, nmcmc, etc... anyways..) when an airplane is 'fmc' its 'green' and i want the aircrafts tail number block in column 'a' to autofill in with a green color, and when its 'nmcmc, i want it to be red. when its 'bq' i want it to autofill in as yellow.
Extraction Of Data From Sheets Based Upon Input Value
Example attachment is included. The goal of this is to extract data off of another sheet based upon an input value. It's easy enough to where the input value will be equivalent to the sheet name I want to reference. The example I provide incorporates code that works successfully. The only problems I have are:
1. The select method only works on the active sheet. I want to be able to call this while another sheet is active.
2. My code is very inefficient and takes a while to execute. I'd like to cut down on the execution time.
Lookup Based On Input Into Textbox On Form
I have a form that pulls up. When the person enters in a ticker symbol I would like the VBA code to perform a lookup in a range "Data_Company_Names" (3000 lines long of company ticker sybols and names) and use the corosponding Name to fill in the textbox for the name. If no Ticker symbol is found then the textbox would be blank.
I realize I would have to use some type of Private Sub TextBox1_Change() code but I am not sure how to have it lookup in a range and return the corosponding name to the ticker. For example, when the form pulls up and the person enters AAPL in Textbox1 then Textbox2 value should equal the Apple which is the company name located in the column over from AAPL in range Data_Company_Names
Overwrite Input Based On Multiple Criteria
I have a spreadsheet that's generated from a download. I need to modify one column of cells based on multiple criteria. Started to try with a nested If statement, but soon passed the limits allowed. Not sure if some kind of VLookup or VBA formula. To summarize what I'm looking for: If column A has a value of any of the following (10D, 11Z, 10Z, 2CS), then I need to change the Rate in column D based on the Years left to maturity in column B. The Rate percentage to be used is based upon the table shown in the attached spreadsheet. In addition, there is an exception if Column A has a value 2CB and an inventory # of 9300276 or 9300277 in column C, then I need to change the rate in column D based on the table as well. Otherwise the rates stay as originally imported.
Highlight Part Of Cell Based On Input
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.
Run A Macro Based On Value Input In A Cell
I 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.
Delete Row Based On Input Box
I am trying to write a macro that asks which row in the active worksheet I want to delete. It asks for confirmation, "Do you really want to delete row" and whatever the user inputted into the input box. It will also color the row yellow so it is clear which row is to be deleted. After confirmation it will delete the whole row and shift all other rows up.
Drop Down Or Some Sort Of Input Cell That Would Change The Look And Format Of The Entire Worksheet
Is there a way to have a drop down or some sort of input cell that would change the look and format of the entire worksheet? Not sure how to pose the question?? For example.
What I'm working on is a departmental forecast sheet.
I want my department heads to be able to all look at the same excel file, then be able to input or select their department from a drop down. Once the department is selected that would call for excel to bring up the forecast for their specific department (**Each departments sheet is a little different).
Email Error: Server Response Was Not Available
I found the below code in one of the posts and it was working fine uptill few days back. now, i get the error Run Time error '-2147220975(80040211) The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available
Dim iMsg As Object
Dim iConf As Object
Dim wb As Workbook
Dim WBname As String
Application. ScreenUpdating = False
Set wb = ActiveWorkbook
WBname = wb. Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"
wb.SaveCopyAs "C:/" & WBname
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults....................
Hiding / Unhiding Rows Based On Cell Input
I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:
>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6
>> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6
Automate Calculation Of Output Based On User Input
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)
I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
Auto-Fill Data Based On User Input
Excel 2003, I have a list of products with corresponding prices located on a hidden worksheet. I have a spot on another worksheet where users can select a certain product and next to that cell there is a column for price. What I want excel to do is auto-populate the price cell based on what product the user selects. For instance, if the user selects OD2000N, then I want the price to auto-populate with the corresponding price that is found on the hidden sheet. Like I said before, I know this has probably been answered, maybe even mulitple times but I can't seem to find the appropriate thread.
Protect Or Unprotect Cells Based On Cell Input
I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?
Here is what I am looking for:
If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16
If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.
Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.
If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.
Code To Delete Rows Based On User Input
I am using the following piece of code to delete unwanted rows from a worksheet:
FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.
However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.
My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?
Some important info: All of the dates are in column C, in the format mmm yyyy
I would only need to narrow down to a month - so for example mar 2008 - jun 2008
Archive Data Based On User's Input
I am trying to move (archive) data from one sheet to another sheet based on a date range provided by the user’s input. If the date on sheet “list” in the cells in range R9:R10000 fall within that date range the following takes place. The rows that meet the date range per the user’s input in the range of R9:R1000 are copied and just the values are pasted into the next empty row starting with row 7 on the “report” worksheet. The cells that are copied on the “List” worksheet are NOT deleted but the contents are cleared. The reason for this is because there are formulas/links in those cells.
I have attached a worksheet example and have some VBA code but it does not work.
Random Option Selection Based On Variable Input
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Sum A Range Of Cells Based On User Input
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
Creating Summation Formula Based On User Input.
I'm trying to create a formula similar to this: =sum(B7:B & 8*A2+3). What I want the formula to do is start in B7 and go to B###. The cell number is based on user input. So if the user enters "24" in cell A2, then 8*24 = 192, 192 + 3 = 195, so the formula would be: =sum(B7:B195). Can I do this in a cell, or do I need to run it in a macro?
Logical Formula With Output Based On The Text Input
Sometime it’s so hard to explain what it is you are having problems with, specially in the little title. I'm trying to write a formula which should be very simple but isn’t . The formula is in box F10 if B10 has anything in it and its going to have a mixture of text and numbers (first text then numbers) then it’s should equal to the value in the box C7. Here is the formula I wrote that doesn’t work.
Sum Year To Date Based On Most Recent Input
My spreadsheet uses alternating columns for actual and budget inputs
I would like to preload the budget for the whole year
But the ytd formula should only show results to match the input of actuals
In otherwords, If I input actuals for period 1, the YTD formulas for actuals and budget should return YTD period 1 only and ignore the remainder of the budget inputs
After period 2 actual inputs, the formulas shlould update to reflect period 2 YTD and so on and so forth
How can I accomplich this?
Sample of layout attached
Totals at Cols AK-AM