I am basically creating a questionnaire style program where the user gets asked 50 questions, one by one and they input their answer and press another button (submit button). They initially press a start button to start the questionnaire.
So far, I have created a range of questions and it fills one row where the questions are entered. In intend to use a for loop to go though all 50 questions.
Now this is where I’m stuck. I have a submit button and after each question is displayed, I want the user to enter their answer and then click the submit button to then continue to the next question.
However, I can’t think of how to do this.
1.Firstly, how can I loop through the questions and yet allow the user to input their answers after each iteration?
2.Also can I even click the submit button during the loop phase? I.e. when already running.
i use excel 2007, it s slow to open a file by double-clicking and entering from windows explorer, by googling i fould a fix by adding "%1" at the end of command in "Application used to perform action" (folder options/file types/xls/advance/open). however, to open files in the same instance, i have to keep "DDE message" with [open("%1")]. With both "%1", excel now opens twice. How to fix it?
I would like to be able to click on the individual cells of a particular column in a spreadsheet and by doing so Excel will automatically enter an "x" into that cell.
The bigger picture is that users can search a spreadsheet for email addresses, an advanced filter posts the results to a separate sheet from which users can configure a group email by clicking an "x" next to the email addresses they want included. Essentially, it's kind of mimicking checkboxes.
I have a multiple of command buttons that I want to make visible. I have the following code which I am trying to get to work. Maybe I am barking up the wrong tree.
I'm very new to Excel and definately to macros/vba. I've already received a tremendous amount of help with this file but I'm am really struggling here and getting very frustrated! I've got a pretty good handle on the excel part but the macros/vba is another story. Here is what i'm attempting to do: I've created a form "frmMacroButtons" which has 8 buttons. When the workbook is opened I want this form/window to launch. I want the user to be able to select any of the buttons but also be able to just click into the worksheet manually if wanted but have the window stay open to go back to use the buttons. Each of the buttons I've typed a description of what I want each of the buttons to do in the code view.
I have set up an option frame with two options. If I click on option A it shows below a frame with txtLenght and txtWidth.
If I use option button B it shows me on the same spot a different frame with txtSQM. I need that so I could either enter the length and width to get the sqm or I do already have the sqm value.
When I set up the code I would like to use an input message saying "Enter Lenght & Width" but if I use the other option it would need to say. "Enter the sqm value".
How or what code do I need to enter to be able to seperate those two from each other.
But also it is important that it does only calculate either the value in sqm or the values from length and width.
I'm trying to write a code to make a number of buttons visible depending on a cell value
I have 10 command buttons all are invisible and I want to show only the first x x is the value of cell "A1" in "Sheet1" (will be from 1 to 10)
Code: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim i As Long For i = 1 To Sheet1.Range("A1").Value CommandButton& i.Visible = True Next i End Sub
I have an input box that request the user to enter a vlue between 1 and 6, and uses the answer to multiply another range of cells.
Now I'm not sure how to add a couple of other features. I would like to: 1. Loop the input box until an acceptable answer is given, (numeric, between 1 and 6), with a message box prompt that says "Try Again". 2. Exit the macro if the user hits "Esc".
Sub Copydata() Dim emre As Integer For emre = 100 To 121 Sheets("CP TB").Select ActiveSheet.Range("$B$8:$G$10000").AutoFilter Field:=3, Criteria1:=emre Range("E8:F8").Select
[Code] .....
I want to skip values when the selection is a value I choose. I tried to put a "If" statement but I couldn't make it work.
The IF statement will check the selection after red colored code. if cell value is "a" then it will go for Next emre.
I have a range (C3:C56). I have a set number in each of these cells. I also have a range out to the side that calculates a number after input a % in cell BI2. The formula reads as "=AE3-(AE3*$BI$2)". Basically it is reducing the values in my range. If a number is in my range is 8 and i input 25% in cell BI2, then my result would be 6.
I have a few steps I want to happen with this range.
1 Display an input box asking for a percentage 2 I want to loop through my range and use that percentage to reduce the numbers 3 (optional but would be amazing to have) after the reduced value is determined, round down to the nearest multiple of 4.
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
I have several hundred workbooks with multiple worksheets(15-35) that I am trying to tidy up, so the data can be entered into a database. Its my first attempt at VBA and between the forum and macro recorder I made an attempt which is below. I just can't seem to get over the last part. I have searched the forum but can't seem to find any relevant information. I have a couple of questions.
1. I need to be able to run this macro over all the worksheets in the workbook, so how can I loop it? All the worksheets are named differently i.e. peoples names.
2. In my code I have inserted 4 columns. These columns will be the only thing common between all the worksheets. A1 to A30 = Week number, B1 to B30 = Shift Number, C1 to C30 = Supervisor number. Column D is blank. I'd like to be able to be prompted to enter these 3 numbers at the start if possible? Can this be done in such a way as you are only prompted the once and not on each worksheet? I picked 30 rows because the amount of rows in each worksheet varies but never exceeds this. The end of my code "Delete rows where cell B is blank" will delete any excess data where 30 rows of week numbers etc are not needed.
Sub CleanCost1_1() ' ' CleanCost1_1 Macro ' Macro recorded 04/09/2009 '
1) input: col A has a series of product code (string) 2) calculation: where I want to automatically copy and paste each product code from "input" worksheet into "calculation" cell B4, and through many steps of calculations, return the outputs in cell B8 and B9 (parameters are numeric) 3) output: how to automatically record B8 and B9 from "calculation" sheet to this sheet Row 5 and 6 from each loop? (format of output doesn't really matters)
The code below first asks a user to input a weekending date (which must be a Saturday). The value of the input box goes to cell C1 of my spreadsheet. That part works fine. I also have a function in Cell G1 with the function:
=TEXT(C1, "dddd") I don't know if this is the best way to test for a Saturday but it is what I have,
Where is goes bad is if the date is not a Saturday. I have a loop to force a new date install via a input box but it won't update C1 with the inputed data to recheck for the Saturday value. Below is the whole code I am working with...
Dim aa As String Dim bb As String
If Range("C1") = "" Then Do While bb = "" bb = InputBox("Please Enter a Weekending Date!") Loop Range("C1").Value = bb End If If Range("G1") "Saturday" Then Do While aa "Saturday" aa = InputBox("Weekending Must Be a Saturday. Please Enter a New Weekending Date") Loop Range("C1").Value = aa End If
I am trying to create a worksheet that has multiple radio buttons. Each radio button is linked to a Macro. I need there to be many buttons running down one side of the worksheet. Each button needs to perform a macro that is relevant to the cells in the same row that it is on.
Essentially what i am trying to do is make a macro that when the button is pressed copys data from H6 and paste it into B6. The button is situated above I6. I need a button for each row from 6 to 110.
While this macro is easy to create (i use the record button and then assign the macro to the button), i would have to do this 104 times and assign a new macro to each button.
Is it possible to change the color of buttons or command buttons? There does not seem to be any place that allows this under properties for buttons, although there does for command bars. However, I've tried recording a macro as I change the color, but nothing get's recorded so I'm not sure what the syntax would be.
I have a spreadsheet with several buttons and I'd like them to change colors as they are pressed so it's possible to see what you've already done. And then, as soon as any other cell on the sheet is changed, the buttons reset color.
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1) Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
With Sheets("regrade pharm_standalone") For Each r In .Range("standaloneTerritory") If r.Value = "X101" Then r.EntireRow.Copy Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues End If Next r End With ------------------- I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
I have a lot of text in a cell and I want to create a Hyperlink using only 2 or 3 words and not the whole text. Unfortunately, it seems as though the hyperlink can only be from the cell itself. Is it possible with Excel to create a Hyperlink from a section of text in a cell, and NOT from the cell itself?
I'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.
I would like to run a macro by clicking a cell. I don't want a toolbar button/menu item and Hyperlinks don't work. Can this be done?
More info: My macro selects the current row in Sheet A and moves it (cut & Paste) to Sheet B in the workbook. The macro then goes back to Sheet A and deletes the now blank row.
Above is the formula that I have, I am putting it into Column L. When I do auto fill every row that has data in J and K the formula goes into as you would expect.
However the formula does not activate until I double click inside each individual row and hit enter. Therefore if the calculation in L4 was 1.0, then 1.0 will appear as the calculation in every row of Column L until I double click in each row individually and press enter.
i got the userform to popup when the workbook is open and the buttons work fine... only problem is when i click on a button, it will ask the user yes or no... if the user press yes, it performs the required macro without any problem... but when the user press no, not only it will close the question, but also closes the userform... I would like the 'no' to close the question but not the userform...
