I have a cell (C16) that has a date format (such as 08/28/2014). I want the value of this cell to be "N/A" when cell C7 is "No". When C7 is "Yes", I want Excel to prompt the user to enter a date for C16 when they click on C16. The formula for cell C7 is =IF(C3="No","No","Yes"). That's where the value of C7="No" comes from. If I didn't need a date for the value of C16, the formula would be something like =IF(C7="No","N/A","______"). I have just recently started trying Excel macros due to a tool
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
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.
Code:
Dim projStartDate As Date Dim projEndDate As Date Dim DateStart As Date[code]....
In column C27 and down, the user can input a date. In column M27 down, the user chooses pass or fail.
N8, contains a date chosen by user as the "From" date and P8 the "to" date.
Cell o11 is "Passed" and cell 012 is "failed"
The user can choose a date range and input the from and to date in N8 and P8, this will count the number of pass and fails and input the number in O11 and o12.
In the spreadsheet shown below I would like the user to select a project via a dropdown list in cell B2 which is from the data set shown in row 7 downwards. Then based on the project they select, they need to be able to see all of the locations associated with that project and choose in B3. Finally they then need to be able to select a team which is associated with the project & location combination chosen in cells B2 and B3.
i have a macro which takes the results generated and puts them into a table and filters the data so that zeros does not show and arranges the table from largest to smallest. Now i have a problem where i need to select the power, fuel oil consumption, weight and area from one table and the other power, fuel oil consumption, weight and area from the other table and combine them together so that i can have all possible combinations of the data, but i don't want the zeros to show in the combinations. i need the combined data to show on a separate sheet. The data of table 1 varies with rows as shown:
The Data of Table 2 varies with rows as shown:
I would like to achieve a result like this in the following columns and rows without having to input them manually:
I've not used Input Box function before. When a user opens my workbook I want to check whether a named range ("Name") is blank and, if it is, to prompt the user to type their name in an Input Box which then returns that value to the named range. This is what I have tried but I don't think I am referring to the named range correctly,
Dim strName If IsNull("Name") Then strName = InputBox("Please enter your name", "Name Required", "1") Range("Name") = strName End If
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))
Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long lngBegin = 9 'beggining of data lngEnd = 232 'end of data lngCmp = InputBox("Please enter the date", "Begining of the week")
My Excel File Name "eai.xls" ,when I Click The File , It Open The Sheet Name "menu" With The Inputbox Prompt:="put Your Id" When The User Put The True Id , It Will Show The Input Box Prompt:="your Password" . If The Password True Will Activate The Sheet Name "data" .and Hide All The Menu Bar.
If The Wrong Id Name Show The Msg.box "re Enter Your True Id " ,that Also For The Wrong Password In Which If User Put More Than 3 Times Wrong , Will Close The Workbook And Exit File.
The User Persons Allow 10 Person Id . Keep In The Table Of The Sheet Name " Id And Password" The Id No. Are In The A1:a10 Password No. Are In B1:b10 This Sheet Name Is Hide And Protect
I run a macro to open multiple workbooks and run a macro within those workbooks. All worked fine until a message/input box was included in the individual workbook macros [that I cannot change] to notify the single workbook user when the macro had successfully completed and prompts for an “ok” input.
Can I get my macro to ignore this prompt and not wait for an input – I am currently clicking “ok” everytime it comes up on screen. Have searched help and forum but nothing jumps out.
I have eventually got my Worksheet up and running but now im trying to make things a little easier to complete. The plan is when the Engineer is on site he has to open the Excel sheet to check the tests he has to complete and then mark the results. this is fine but for vaildation he has to sign everthing etc... i made it easier so they only had to fill in one box and it would complete the rest but this seems to get missed more often than not.
SOOO
i want a Screen to pop up when some one opens my excel that says Enter name and contact details. then they submit this which updates the spread sheet with this information and then the engineer can continue as normal.
I have created an addin that runs several macros. To enable the user to undo any changes, I keep a backup prior to them using any of the macros in the form of a worksheet named back00xx. I want a prompt to be displayed to the user to remove these backups at the closing or saving of the file. The problem is that as an addin, how do I get it to display this prompt as the open workbook is not part of the addin.
Could one have a looping routine that is checking every xx min/sec to check if the current workbook contains any sheets starting back00
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 '
I would like to create an spreadsheet that prompts me to input data into a cell (eg; 'C1' - 'Quantity Ordered') as soon as another cell on the same row becomes populated (eg; 'A1' - 'Item Code'). I need this function to apply for several rows (25 rows in total), so that if data is then entered into cell 'A2', I get a prompt to enter data into 'C2', all the way up to a prompt for data in cell 'C25' when cell 'A25' is populated.
As it is imperative that I never forget to populate the relevant cells; I would also like to 'enforce' the prompt - perhaps by preventing the worksheet from being saved until the relevant cells have had data entered into them, or perhaps by using some kind of form, rather than a messagebox.
This file pulls totals from external links. The way I have it set up is that every month our log is saved to a naming scheme involving the date, then a fresh log is created for the current month. So, for each month I have "log mm-yyyy".
Now, the chart file is a thirteen month rolling chart (meaning every month it must show the thirteen most recent month's totals). I have created a macro to automatically update the links, based on an input in cell A:1 (meaning you can enter a date in the past and view the thirteen months previous). The problem is that we have only been doing the log for 10 months. So, when the macro runs to update the links, and it gets to months 11 through 13, it pops up a window for the user to manually browse for the missing(non-existent) files. There are quite a few cells that contain links to various information on these logs. I get a prompt for each cell and have to click "cancel". Once I have clicked cancel through all of the broken links, then everything works great.
My question (after all that) is, is there a way to suppress the prompt for the user to search for the missing file? Like I said, this chart is for management, and they shouldn't have to click cancel a dozen times just o look at a chart. If the file doesn't exist, then just break the link.
Obviously once the next three months are over, this problem will disappear, but in the mean time I need a solution.
I'm building a spreadsheet that consolidates multiple worksheets into one. I've got that portion done, but what i need help with is a user prompt. I don't have an example at the moment, but it should be fairly straight forward.
Rather than consolidating all the data into one worksheet I want to consolidate only the rows that contain certain information. The column I need to query in each worksheet is "BL". The only thing is that column won't always contain the same data. Is there a way to do basically a prompt to where it searches for the data they want and if it isn't in there it just moves on to the next worksheet automatically?
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.)
I am looking to allow a user to enter a unique value when a drop down value is captured from a list i.e 'Other' (Possibly a pop up box), then I require this value to be hidden and calculated along with others to show the average (The average calculation obviously wouldn't be in the same cell).
My new Time in Motion sheet is coming along nicely with some snazzy programmed Command Buttons to fill data into another sheet.
One of my buttons is for 'other' tasks, so I'd like to programme a button to prompt first for a number of minutes (e.g. 30), and then a brief description (e.g. Meeting) and input the data side-by-side in 2 columns on sheet 2.
This is what I have for my other single-entry buttons, so what would I need to add?
Dim ans As String ans = InputBox("Case reference:", "Data Entry") Sheets("Sheet2").Range("F" & Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row + 1).Value = ans
Looking to write a macro which includes a prompt to the user for use in Excel 2007. Specifics....Have a workbook including many worksheets with a common layout. Would like to write a macro to copy specific defined ranges from one worksheet to another but within the macro it would prompt the user to type in the worksheet name to copy the ranges from.
Example....User is at worksheet C in the file containing worksheets A, B, C, and D. User starts the macro and a prompt comes up for the user to input the worksheet name to source the ranges from (in this example A). User types in "A" in a dialog box and the macros copy/pastes the contents from range B4:B35 and Z2:z20 from worksheet A into those same ranges in worksheet C.
I am using VBA to create a weightloss calculator and everything is going fine ecept the Vbyesno boolean that will not allow me to delete the data that i want.
I used a macro to delete the information that i want to delete and i know it works because without the vbyesno, it works fine.
With the vbyes no, both yes and no do nothing when clicked and the yes no box just closes.
The macro code is stored in a private sub called "ClearAll"
My code is:
Private Sub Restart_Click() Dim Response As Boolean Response = MsgBox ("Are you sure",YbYesNo) If Response = VbYes Then Call ClearAll End If End Sub
What am i doing wrong?
I also want to make the no button the default, how do i do this in conjunction with the code above?
I've recorded this code and am looking to include it in a button. I would like for when a user clicks the button assinged macro that the active sheet is cloned and saved as a CSV file. The user should be prompted before saving on where (file location) they'd like to save the file.
Sub CloneWorksheet() ' ' CloneWorksheet Macro ' ' Sheets("SDW&Customer Workshop scheduled").Select Sheets("SDW&Customer Workshop scheduled").Copy ActiveWorkbook.SaveAs Filename:= _ "C:Documents and Settings1167916My DocumentsTestBook1.csv", FileFormat:= _ xlCSV, CreateBackup:=False End Sub
I have got a protected sheet with macros, how can I ensure that users can only open the sheet as Macro enabled only otherwise the sheet would not open?
I understand that some users may have different Macro security settings?
I need a macro which collects data from two files and dumps it into a separate spreadsheet. I need it to prompt the user to select the two files, since the file names may change.
Once a user selects the files, the macro simply opens them, grabs the data (the data sits on a single sheet in each file) and pastes it into a file, and closes the 2 data files.
Basically, in the "Thisworkbook" code , i have some code in the Workbook_BeforeClose section. Currently , it autosaves the workbook in a folder i have specified.
However, i need to add some code.I want to check that a certain cell has a value in it before the user closes the workbook, and if the cell is empty, show a messagebox asking him to enter a value.
I know how to get a messagebox to pop up, the only thing is once the user clicks the OK button, i need the rest of the code execution to pause, allowing him to make the change then if he clicks the "X" (top right of the screen) to close the file or application, the filesave dialog appears and he can then save the document.
how to go about this because at the moment when user clicks ok, the messagebox just disappears and filesave dialog appears and he doesn't have a chance to edit the cell.
