Stop Input Into Worksheet Based On Yes No Response
Mar 4, 2009
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 5 Replies
ADVERTISEMENT
Jan 21, 2014
I'm working with a substantial amount of code (which, due to its size, has been spread out into different modules). Whenever I transition from one module to the other, I have placed a msg at the beginning of each module to prompt the user to simply insert an integer to signify which version of the automation that is run. The following is an excerpt of a sample msg
[CODE]
Dim Msg, Title As String
Dim MyInput As Integer
' Define message."
[Code].....
Where, whatever report is being run, will be the same throughout all of the modules (for example, if you run the "Day" Report" at the beginning, the rest of the automation should be run using the "Day Report" code, not the "Night Report").
Is there a way to do one of the following
1. Label the input of "Day Report" or "Night Report" at the beginning of the automation (where the above except of code is how that would be done), and that can then be the determinant through the rest of the modules
2. Set my code in a specific module to, when it completes, automatically go to the same style of report (Day or Night) in the next module.
View 1 Replies
View Related
Jul 25, 2008
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 9 Replies
View Related
Mar 27, 2014
Essentially what I want, is to type a medication in, lets say, A2, and for a macro to produce a response in B2 based off of grouping criteria from another worksheet. My workbook better explains what I would like to see happen. A macro would be best for this, right?
View 7 Replies
View Related
Dec 5, 2013
I wonder how I am supposed to do to stop excel from returning the error message "Runtime error 13" when pressing the cancel button on a Inputbox.
View 3 Replies
View Related
Feb 23, 2014
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
View 6 Replies
View Related
Dec 9, 2008
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 9 Replies
View Related
Oct 4, 2009
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 8 Replies
View Related
May 27, 2007
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 9 Replies
View Related
Sep 27, 2009
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 8 Replies
View Related
Sep 12, 2006
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 9 Replies
View Related
Aug 17, 2009
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 6 Replies
View Related
Aug 31, 2006
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
Next
End Sub.......
View 3 Replies
View Related
Dec 13, 2006
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 4 Replies
View Related
Apr 26, 2009
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 6 Replies
View Related
Dec 17, 2007
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.
Example:
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 9 Replies
View Related
Jan 27, 2014
I am trying create a macro which asks for the Sheet name as an input for a Sub. I've gotten it to work but there is just one problem: The Cancel button does not work on the MsgBox. Everything else works just as I want to: it shows a dialog box to enter a name for the worksheet and if the worksheet does not exist, it loops and shows a message saying that it does not exist.
However, the cancel button does the same thing as entering nothing in the box and it does not end the process. I would want it so that the cancel button kills the process. Here is the code:
Code:
Option Explicit
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
[Code] .......
View 2 Replies
View Related
Aug 31, 2008
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 9 Replies
View Related
Jan 18, 2008
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
6
7
View 8 Replies
View Related
Aug 11, 2008
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 14 Replies
View Related
Oct 13, 2008
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 4 Replies
View Related
Nov 16, 2008
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.
View 7 Replies
View Related
Jan 4, 2009
if cell a24 has nothing in it how can i make sure a77 is blank at the moment it returns a zero
View 2 Replies
View Related
Jan 27, 2009
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 2 Replies
View Related
Dec 14, 2012
I have this formula: =(CONCATENATE(COUNT('Master Report'!R8:R34)," ",IF(COUNT('Master Report'!R8:R34)>0,"Departments are over Action OI 50%","")))
when there is nothing to count, it places a 0. I want to to be blank. How can I achieve this.
View 5 Replies
View Related
May 10, 2006
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
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=MaterialList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = False
End With
View 9 Replies
View Related
Sep 27, 2007
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 3 Replies
View Related
Mar 24, 2009
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 2 Replies
View Related
Nov 25, 2013
I want to return the data from a list in a cell on worksheet1 if I input a variable on worksheet2. I need to avoid VBA and pivot tables.
Example, if I put "Expense" in the input cell on worksheet2, I want to return a list of each row that has "Expense" in it on worksheet1:
Worksheet 1 (ColumnA/ColumnB):
Revenue/45,000
Expense/20,000
Asset/43,000
Liability/21,000
Revenue/6,000
Expense/9,000
Expense/11,000
Liability/13,000
Worksheet 2 required output (no row gaps or spaces):
Input cell "Expense" - in A1
List required (A3:B5):
Expense/20,000
Expense/9,000
Expense/11,000
View 9 Replies
View Related
Jul 30, 2014
I am trying to get Excel to search A1 and B1 for specific information and return a result in C1.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "4"
Then
C1 will produce text of "170"
But the formula must also detect and return a different amount if the information in B1 changes.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "7"
Then
C1 will produce text of "250"
View 5 Replies
View Related