VBA Macro Event For Questionnaire With "X" & Yes/No
Dec 4, 2008
I have created a worksheet to track/calculate compliance with a number of important items at a number of business locations on a number of questions. This workbook will be posted on a central server that will allow 25+ employees to access it from the field and "double-click" to enter an X in either a "Yes" or "No" column for about 100+ locations (the code also will remove the "X" in either column if another "X" is entered into the other column to prevent double entries). The workbook will be quite elaborate and will be locked down except for those cells unprotected for data entry (identifying location, date, etc.).
Below is the code and it works just fine as written with two columns to chose from to "double-click" and enter an "X" (columns C & D for rows 4 thru 15). I want to "freeze frames" locking columns A & B and allow scrolling to the right to enter each New Location and allow the same code to apply to the same rows but column groupings of E & F, G & H, I & J, ...until I have about 100+ sets of two columns identified in the code.
I have tried a variety of changes to the "Union(Range" and "Intersect(Target, Range" last night and this morning to no avail and have gotten nothing but a headache and a desire to drink heavily from the exercise.
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("C4:D15"))
If Not rInt Is Nothing Then
For Each rCell In rInt
rCell.Value = "X"
Next
End If
Set rInt = Nothing
Set rCell = Nothing
Cancel = True
I am designing a skill assessment questionnaire and want to use radiobuttons to keep it user friendly. I have about 100 line items with 6 radiobuttons and 1 checkbox. I want to assign a linked cell to each button and group the radiobuttons of each row (plus set a default value.) As the number of buttons is large I am looking for a way to do this efficiently without manually changing the GroupName in the properties window. Currently, I create the buttons automatically but cannot change the desired attributes (groupname and linkedCell) unless I explicitly address the respective button by its name directly.
This it not practical though due to the large number of buttons in the spreadsheet. Currently I try to address the buttons with other variables but this does not work. my current code is the following (for only 5 rows and 2 buttons per row):
Sub Buildbuttons() Dim RowNo As Integer Dim i As Integer Dim x, y As Integer Dim xdif As Integer Dim ydif As Integer Dim LinkOB1, LinkOB2, LinkOB3, LinkOB4, LinkOB5, LinkOB6, LinkCB1 As String Dim OB1, OB2, OB3, OB4, OB5, OB6, CB1 As String Dim MyRange As String ' now of rows to fill RowNo = 5 'define starting position and distances x = 20 y = 5 xdif = 54 ydif = 18 i = 1.................
I am trying to develop a questionnaire spreadsheet using a ranking system. I have different questions (based on certain criterias) and four columns for each question: NEVER | SOMETIMES | MOST OF THE TIME | ALWAYS.
I want to see if there is a way that I can lock the other three cells when the user has chosen a particular column by typing an X. BY doing so, I want to ensure that the user will not choose two options (or columns) instead of only one, because if he/she does it will mess the other calculations that are not shown within the table. Only the input and output are shown.
As part of an audit program I have a questionnaire. One of the questions has 10 check boxes, one with a combo box and one with a text box. The user will check at least 2 or more answers, but not all 10. (2-4 average). I don’t want to link the answers to specific cells, because I don’t want the answers to display with blank rows between answer on the sheet. I want the answers to display in 2 columns evenly distributed.
I would prefer, to not have to run a macro, if possible, but the only way I could see to do this is a very long if statement base on every combination of answers. If the only way to do this is thru a macro, I would prefer to tie it to the printing of the sheet, or I could make it part of an error checking macro I plan to do. The actual question is in the attached file
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
I need a before save event macro in excel that will check if sheet1 cell ref A25 = zero. If it isnt zero I want a msgbox to say "Does Not Equal Zero" to pop up, if it is zero then I do not want a msgbox displayed. In both instances I still want the file to be saved.
I have a situation where I have a macro that sorts on sheet1. I would like it to run when a cell changes on sheet2 in E9:N21. Since the cells on sheet1 are linked to data on sheet2, I cannot trigger the macro from sheet1 due to the cells being formulas instead of values.
Here is what I have tried and it is not working. I must be doing something wrong with the syntax.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range(Sheets("Games")("E9:N21"))) Is Nothing Then Exit Sub Else ActiveWindow.SmallScroll Down:=33 Rows("6:55").Select Range("A55").Activate Selection.Sort Key1:=Range("A55"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A3").Select End If End Sub
The macro below works great as a conditional format routine on the Input sheet of the workbook, however I would like it to run on the Data sheet as well, but with a different range (E3:CE117). Can this be done?
Private Sub Worksheet_Calculate() Dim dcell As Range Dim icolour As Integer For Each dcell In Range("c15:g27") Select Case dcell.Value Case "GA" icolor = 43 Case "GAC" icolor = 4 Case "LA" icolor = 45.....................
i currently have a workbbok that i have a very simple worksheet event macro that will filter a seperate sheets contents to the corresponding entry number. is there any way to make a dynamic event macro that will filter my second sheet depending on what cell is selceted on the first.
for example record 1 on sheet 1. if cell L1 is selected it will filter sheet 2 to to record 1, record 2 on sheet 1, if cell L2 is selected it will filter sheet 2 record 2 and so on. this list of records will continue to grow and i cant think of any way other than what i currently have, which is to make each filter macro for every cell.
is ther a better way example of CODE HERE
VB: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Address = "$L$5" Then Call Filter1
I need my macro to run when the spreadsheet is Saved. Is that process written in the macro code or is there an existing event control in Excel to accomplish this? Running Excel 2007. I tried searching the forum & found one post that ran the macro on open, but couldn't set that up to work on save.
with an event macro. Basically I have a workbook with 11 worksheets and have created a macro that increases the value of cell J3 by 1 in Sheet1. However at present it only works from Sheet5, but I now need it to run from Sheet6 and Sheet11 as well and not from any other worksheets. The code I have is as follows
Private Sub Workbook_BeforePrint(Cancel As Boolean) If ActiveSheet.CodeName = "Sheet5" Then With Sheet1 .Range("J3").Value = .Range("J3").Value + 1 End With End If End Sub
I have a worksheet called "Roster" with a list of names from D7:D17 each person on the list has a separate sheet that is called by their name. the sheets are not linked up. the names were just manually typed into the list and into each sheet name.
If any name is changed on the list on "Roster!D7:D17" i would like the sheet that has that name to change as well.
This is the code that i was sent, but i do not know enough about VBA code to understand exactly what is happening, thus i cannot diagnose the problem. I placed the following code into the page "Roster" (by right-clicking and then pasting the code) then i closed, but nothing happens. I can mess with the code and get an error message. but that is about it. error messages usually say something about the debugger, or runtime error or something.
there is the Private Sub Worksheet_SelectionChange(ByVal Target As Range) OldVal(0) = OldVal(1) OldVal(1) = Target(1).Value End Sub
how to make a code so that it captures the event of pressing any key of the keyboard and triggers a macro.
I tried using OnKey method but the problem with this is I am getting stick to any particular button whichever i specify in the onkey method wheareas I want to keep it general for any button in the keyboard.
Also if anyone can suggest how can I use OnKey method to use the "S" key to launch a macro.
I'm using a macro that picks up on changes in a active cell.. This is the code
HTML Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then If Target.Value <> "" Then
ThisWorkbook.Sheets("Log").Cells(Target.Row, 2).Formula = "=Today()" End If End If End Sub
There's a problem with this code, when select from a range and hit delete, i get a error mismatch. I want to have the macro ignore when i select active cell range of greater then 1.
I have not used an event macro before and am trying to one update a cell when changing a cell. I am basically copying a number to another cell that is an input for a calculation and then returning the calculated value back. How do I reference r69 in the code to start the event macro?
In Col D of my spreadsheet, I have a list of security codes, in this list there is a security code "all", i need a macro that will add 1 to the code, so it reads "all1", now i need the macro to run as soon as new data is pasted to sheet "Lending", the ranges in Col D do change on a daily basis therefore cell reference for security codes is not fixed.
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
I have a real simple question about what command to enter in order to stop a macro. All the macro does is "press the F9 key" every second. I use a button to start the loop but I am unable to find a way to use another button to stop the process. Ive copied my macro below:
Sub Go() Calculate Application .OnTime Now + TimeValue("00:00:01"), "Go" End Sub
Private Sub CommandButton1_Click() "Go Button" Application.OnTime Now + TimeValue("00:00:01"), "Go" End Sub
Private Sub CommandButton2_Click() "Stop Button" '???????????????????????? End Sub
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.
The final thread is here: Message To Remind That Cell Is Mandatory
and the Macro used is:
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range Application.EnableEvents = False For Each c In Target If c.Column = 11 Then If c.Value = "100 - Purchase Order In" Then MsgBox "Is the Month In correct?" End If End If If c.Column > 1 And c.Column < 18 Then Cells(c.Row, 1) = Now End If Next c Application.EnableEvents = True End Sub
However...
We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!
Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
I have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.
I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.
My purpose is "orange" is printed out with a reference number.
Working in Excel 2003. I have a VBA code that, if a particular option is chosen from a drop down box, then a message box appears. What I'd like to do is alter this code so that if cell J5 has "Text 1", "Text 2", or "Text 3" then the message box does not appear. Here's my
