I have searched for code in the forum to help me here but could not get any of the possible choices to work for me. I have a spreadsheet that is password protected but would like the user to be able to change the color of the fonts based on the user's choice. The protected page (one of many in the spreadsheet) has various cells that are unlocked so that the user can enter data. The rest of the page is locked. I have allowed all users of the worksheet to Select Unlocked Cells only. I have attempted to allow them to Format cells as well, but each time the program is re opened this feature is no longer working. (The program has a macro that ensures it opens in protected mode each time.)
So I would like to set up a Macro that allows the users to set the color of 6 rows and 5 columns based on their choice. An example of the configuration is:
Using the above format, I am imagining the I would have a Key with color codes (using the ColorIndex Properties). The user would type in the appropriate number for each row and click a button and the rows (5 cells each) would change to the selected color.
This code works fine if i use static ranges, but i would like the user to input the column in which the data is stored.
'Get data for the locations of the gaming license numbers needed for the comparison varPlayerHost = InputBox("Please enter a single letter for the" + vbCrLf + _ "Column that the Player Host License" + vbCrLf + _ "numbers are in.", "Player Host Number Location", "H") varHostLicense = InputBox("Now enter the column letter for the copied employee" + vbCrLf + _ "license numbers", "Employee License Number Location", "U")
'Set the ranges for the data to be compared 'This is where I am getting the error??? Set rRangeA = Range([varPlayerHost,1], Range(varPlayerHost, 65536).End(xlUp)) Set rRangeB = Range([varHostLicense,1], Range(varHostLicense, 65536).End(xlUp))
'The actual comparison and deletion of record that match the license numbers copied. 'When this function finishes you will be left with a spreadsheet that only has patron 'information left for the patrons that have an invalid host number. For Each rCell In rRangeA If WorksheetFunction. CountIf(rRangeB, rCell) > 0 Then rCell.EntireRow.Delete End If Next rCell
This exact code is kicking an error "Run Time error 1004" "Method 'Range' of Object '_Global' failed" Not quite sure where i went wrong.
I'm trying to get a users' selected range to display in a cell as stored text.
therefore, If a user selects A1:D7. The text in Cells("G", 10) will be:"A1:D7"
w/o the quotes.
I've attached an example workbook. Here's the formula thus far:
Sub SelectRangeBox() Dim rnBody As Range Dim vaMsg As Variant Do vaMsg = Application.InputBox( _ Prompt:="Please enter the message-text:", _ Title:="Message", _ Type:=2) Loop While vaMsg = ""................................
Actuall maybe my thread should be Titled. user selected cell range displayed in input box save to cell as a text value.
If possible could someone please help with changing the below code so that column "D" in the rngIn part of the code can be a variable. i.e so the user can select one, two, three or four columns (with visible data) to be copied. The "b3" part is a constant.
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.
1) Can a userform procedure be place in the midst a larger macro, so that it can get input from the user before running the rest of the macro? ie If the user leave the input field blank, the macro will exit will a msgbox.
2) I have a range(A1:A3) that i want my userform to display. This range is dynamic. The user will require to input the corresponding country next to it. how do i code the this? I will need to use a listboxes and textboxes right?
for example: userform shows city on the left, user will need to input the country on the right.
A1 - California (user input on the right) A2 - Shanghai (user input on the right) A3 - Mumbai (user input on the right)
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Dim projStartDate As Date Dim projEndDate As Date Dim DateStart As Date[code]....
There must be a VBA command out there, with which one can call the color panel such that the user can then choose his/her preferred color, I just cannot find it. Could you help me out?
With Color-Panel I mean this little interface you get when manually changing the font-color or the fill-color. (it consits out of maybe 8 colors in a row and 5 colors per column and usually has a button "automatic"). I would like to call that interface such that it gives back the color-index of the color choosen by the user.
How do I pause my macro until the user selects a chart in the worksheet? Or Keep prompting a message to the user till he selects the chart and when a chart is selected run the macro.
Step 1: Check if a chart is Selected Step 2: If yes, run the macro Step 3: If no, prompt the user with a msgbox of Retry/Cancel. Step 4: If the user selects Retry, wait till he makes a selection. Step 5: If the selection is a chart, run the macro. If the selection is not a chart prompt the same msgbox again and keep looping it till he selects a chart.
Sub test5() Dim chtSelected As Chart Dim UserResponse As Integer
On Error Resume Next Set chtSelected = ActiveChart
I would like to do a Macro which requires user to go to a specific line which is variable in each work sheet. I would like to be able to enter the number and then delete all lines below that point and then continue with the balance of the macro.
Is there an easy way to pause the Macro and then restart it? Or is there a code that I can insert in the Macro to locate a specific Cell and then delete all lines below the next line?
In my macro, I'm trying to use an input box to determine the factor to multiply another cell by. I also want to only allow entry of 6.0 or less, and only allow, (but not require), a single decimal place. Here's what I've got so far, but it's not working:
I would like to make a button on my toolbar that will let me autofill whatever cell I've selected down to whatever row I designate. I would like to have a user input where you put in the row number and it autofills whatever formula down to that row in the column selected.
I know how to do a normal autofill, but I don't know how to do a user input or have it input into the autofill process.
If anything else is entered into the input box a message box (MSGbox) with a vbCritical button will appear with the following prompt "You entry (insert entry data here) is inccorect." I don't know the code for "insert entry data here".
I would like a cell to have user input. If there is no input then the cell should reference a value in another cell. My strategy was to use a formula in another cell to check to see if the user input cell was empty. If it was empty then the formula propagated the cell. If it had user inputted data then it stayed like it was.
Here is an example: Begin Invent 1000 Gallons
End Invent User input or formula here Gallons
I want the user to be able to input a value for the End Invent cell right below 1000. If there is no value I want that same cell to mirror the cell above it with the 1000 in it.
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
I need a cell to display a default value (something like "Input Here") which a user can overwrite with a value. When the value is erased, the cell should return to the default. Preferably, it'd be great if the cell reset to the default value whenever the workbook is opened as well (i.e. won't save the user's input value).
I am trying to build a user-friendly Excel database that takes the user's input from various cells and logs that data input by the user in successive rows to the side. Please see the screenshots of my current spreadsheet below:
From the screenshot above, I would like for the user to input their data for the Date of Last Inspection, Current Hours, and check the boxes for any services they performed in the orange/pink "Input" box on the left. Currently I have the spreadsheet set up so that the orange/pink "input" areas recognize data put in to the yellow "Data Log" cells to the right. I am trying to get the yellow "Data Log" cell area to store the Date and Hours data put in by the user in successive rows like a database, but I am not sure how to do this. I was thinking of putting a button in the says "Log Data" which would, when clicked, store the data in the "Data Log" cells. I am unsure if I need to use a Macro or whether Excel has a simple, built-in function that can be used. Eventually I have plans for the "Date of Last _______ Change" Gray boxes, but would like to take this one step at a time.
I am not well versed in VBA and Excel Macros, and I am currently using Microsoft Excel 2007 (yes, I know it is old). I do have C# and Matlab coding experience, however I would like to do this in Excel to make it easier for the User as it is a well known program.
I have a macro that's running quite well and is cross-posted here:
What I need to do is allow for user input. I want to create a dialog box that pops up and asks for an input range, when the user runs the macro. Values can only be from 1 - 50. And the user won't be allowed to select broken sequences, like 6, 9, 12-15.
They would have to be continuous, i.e. 6 - 15, etc. I'm still very new to VBA. I can play with the control toolbox, but I have very little understanding of how to interface with the components that I'm adding/creating. Once I have that range, I want to use the inputs as the 'start' and 'stop' points for my loop. So I need to capture the values and pass them as input parameters, I'm guessing.
I can modify the loop to accept the inputs, I'm just not sure how to capture them from a user input dialog box.
Due to the economy we are only scheduling days to work one week ahead which leads to alot of phone calls. The gaurds who get the calls then search thru a printed spreadsheet looking for a clock number or last name. I realized right away they should just look at the file on the computer and due edit - find. The problem is they due not use computers unless printing and they looked at me like I was talking in a different language. So I want to edit this macro below so it will allow user to type in search criteria instead of using same search criteria every time. Here it is 70039. I also need to place a shortcut or button to this macro on the toolbar or sheet and do not know how. How do I stop "remove macro or enable when file is loaded"?
Does VBA support any type of "browse for file" interface? I'm thinking something similar to an inputbox, but instead it gives you the standard looking windows explorer window to find a file (think adding an attachment in outlook). The function would return a string.
I work in the Quality dept and I have came up with a gauge calibration workbook. I am in need of a command button to get an user input "date" and sort the data and print by that date. What I have now is you have to manually sort by the date needed and copy and paste to a worksheet.