Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

Sponsored Links:

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

View Replies!   View Related
How To Change An Input Box Based On Input In Another
What I am trying to accomplish....

If the input changes in D5, K11 will reset to $0.00..

Example. If cell K11 is set to $300.00, and the corresponding input in D5 is '123', when I change the contents of D5 it sets K11 back to '0.00'.

View Replies!   View Related
Stop Switching To Different Worksheet
I need help with stopping the screen to swiching to different worksheet. Everytime I execute this macro, the screen is switching to different worksheet.

View Replies!   View Related
Stop Textbox Deletion On Worksheet
How do I stop a text box called "Text Box 4" from being deleted. It was created from the drawing toolbar. I don't want to protect the worksheet if possible.

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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
End Sub.......

View Replies!   View Related
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)

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
Msg Box Response Is Ignored
I am trying to achieve a response from a msgbox which will either continue with the sub or exit. I think my code is right but it doesn't seem to work and just carrys on regardless.

View Replies!   View Related
Response Log
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

Two questions
1 - what is a good title for this?
2 - how is it done?

View Replies!   View Related
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) if somebody inputs defects by date it should automatically cumulate into respective month.

View Replies!   View Related
Select Only One Response
Trying to accomplish a bit of data validation...

4 cells
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.

View Replies!   View Related
Stop Macro: Button To Hit Or Better Just Some Keys To Hit To Stop It Without Using The Ctrl+alt+del Which Closes Everything
my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.

View Replies!   View Related
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".

View Replies!   View Related
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.

View Replies!   View Related
VBA Code To Copy Data From Text Box And Input Into A Cell In Another Worksheet
This is probably far easier then I am making it but I need to take the information that is in a textbox created through the control toolbox and copy and paste that information into a cell on different tab in the workbook. I have tried the infamous google and haven't been able to find much. The excel books that I have don't really touch on the control toolbox functions.

View Replies!   View Related
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.

Example 1
Column A = 2
Column B = 0
Column C = Y

Example 2
Column = 0
Column = 0
Column = N

View Replies!   View Related
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:= _
xlBetween, Formula1:="=MaterialList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With

View Replies!   View Related
Return A Blank Response
if cell a24 has nothing in it how can i make sure a77 is blank at the moment it returns a zero

View Replies!   View Related
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"
End If

End Sub

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?

View Replies!   View Related
Copy & Insert Row Based On User Input
I 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?

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

Sheet 1:

A1 = Day of the week
A2 = Unique primary key
A3 = user input value

Sheet 2:
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).

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Cell Based On Formula And Allow User Input?
Cell 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 Replies!   View Related
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

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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).

View Replies!   View Related
Initiating A Response When Entering Into A Cell
I have an Excel worksheet that when a user enters something into cell A1 I want to clear cells (b2:b20). If I use an object, I can attach a macro to it, but I don't want to use an object. I want to use an Excel cell and then when the user types into it, I want to perform an action.

View Replies!   View Related
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

Sub CDO_Send_Workbook()
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....................

View Replies!   View Related
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

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Insert Row Based On Cell Value :: Give Input Box On Click
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 Replies!   View Related
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.

View Replies!   View Related
Code To Delete Rows Based On User Input
I am using the following piece of code to delete unwanted rows from a worksheet:

Sub DeleteDates()

FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If

Next i

End Sub

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

View Replies!   View Related
Populate A Cell Based On Input From A Drop Down List
Various Numbers Will Be in a drop-down List eg:

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.

View Replies!   View Related
Lock/unlock Cell Based On Another Cells Input
I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)

View Replies!   View Related
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.

Previous discussion:

View Replies!   View Related
Copyright © 2005-08, All rights reserved