Is there a way to force the inputs of a pop-up window to be a certain format? I have a macro that copies and renames a sheet based upon user inputs....but it only uses a pop-up window with dialogue only explaining that that is how they SHOULD name the sheet (DD-MM-YY) without using restricted characters. Moreover, it would be great if it would check the name against names already in the workbook to prevent an error for duplicate sheet names or force it to be whatever DD-MM-YY they input as DD-MM-YY(2) etc.
Sub CopyandRenameSummary()
Application. ScreenUpdating = False
'Copies and Renames Summary
Sheets("Summary").Select
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
On Error Goto ErrorHandler
Dim Myinput
Myinput = InputBox("Enter the Count Date in the format DD-MM-YY (INCLUDING DASHES). You cannot use any of the following symbols: : / ? * [ ]. You cannot prepare more than one count sheet per date input.")
I need a formula that I can put into a cell, I assume via data validation, and will prevent the use of any symbols other than a hyphen "-". Spaces are fine. The cell is being used to enter the 1st line of an address.
My 10 MB excel file has calculation mode always automatic. It has 100 worksheet. Only a few of these worksheets are enable for calculation, for performance optimization.
Therefore, I use the macro: Worksheets("sheet1").EnableCalculation = False When needed, I do: Worksheets("sheet1").EnableCalculation = True
Unfortunately, from time to time, the calculation does not work. By chance I realize that by insterting a cell in this worksheet, the EnableCalculation became active. My problem is that I feel this is happening randomly, depending on worksheet, maybe depending on users or on sequences of entries.
I want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.
I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
how to merge data automatically in one sheet (master sheet) from specific sheet with different name sheet but same format?
Adding, deleting or editing should be automatic update to master sheet.
For example:
Sheet name:
Sheet 1: Salesman 1 - with data range A10:F310 (as his data file) Sheet 2: Salesman 2 - with data range A10:F310 (as his data file) Sheet 3: Salesman 3 - with data range A10:F310 (as his data file) Sheet 4: Salesman 4 - with data range A10:F310 (as his data file) Sheet 5: Salesman 5 - with data range A10:F310 (as his data file) Sheet 6: Salesman 6 - with data range A10:F310 (as his data file) Sheet 7: All Salesman - - with prepared data range A11:F2000 (as master data file/record for 6 salesman)
Column Name as a Header:
A10: Customer Number B10: Customer Name C10: Salesman ID D10: Promotion Type E10: Promo Item F10: Free Item
I'm trying to write a macro that will not allow a number to be zero. I have a macro that references a cell that if it is input to zero will create a loop that goes on forever. The thing is this cell will never have to be zero, so is there a way that I can write a macro that if this cell is zero, it gets reset to its previous value?
I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.
ex: cells A1, B1, C1 if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.
I'm looking for a code that automatically changes the format of any pasted in data to Match destination formatting. Not getting the results I want from protecting the sheet.
I have a ctrl-q key activated macro (module 2) that successfully clears specific data entry cells in forms on two identically formatted sheets. How do I prevent access to that macro on the remaining three sheets of five in the workbook?
How can I restrict the user from entering anything other than the date and the proper format required for this input box? I am having a tough time figuring out how to check the input for the right date and format . .
Sub EffDate() Dim a As Variant
a = InputBox("Enter the effective date" & _ " of the subject's lease using a 2 digit day," & _ " a 2 digit month and a 4 digit year" & _ " (dd/mm/yyyy).", "C/NC Analysis")
I need to modify it as it currently allows the user to save even if there is no values in D2 or D3. I need it to look at these cells first as well as A17-F25 and if there is no data in any of these cells it either brings up an error message saying you need to enter data in these cells to save!
Also, at present the user has to click on the default save button within excel to save changes made after the initial save, could this be added to the button I have created?
I've a workbook (XL 2003) test-1.xls, and I need to restrict it to a specific computer with ID: my-a1234567k
(shown under ControlPanel::System::Computer Name::Full computer name).
Is it possible to code such restriction in the w/b Open() event to prevent opening the w/b on a different computer?
My computers each has a different OS (Win 2000, Win XP Home, Win XP Prof ), different Excel version (XL 2000, 2003, 2007), etc., and the majority of my workbooks have been developed and would work only in certain environment (e.g.; in XL 2003 only, or XL 2007 only, ... ).
I am looking for a macro that would accept only one entry per selected ranges. It would be something like if there is one entry in range("b4:e4"), a message box would appear advising the user that only one entry is permitted in that range. It would give the option to delete the entry and to rekey the entry for that canditate.
I hope I am clear, if not let me know 1 entry in range("b4:e4"), per canditate 1 entry in range("f4:h4"), per canditate 1 entry in range("i4:l4") per canditate 1 entry in range ("m4:o4") per canditate 1 entry in range ("p4:r4") per canditate 1 entry in range ("s4:v4") per canditate
I have a survey with 2 checkboxes for each question. Users need to tick only 1 checkbox for each question. The checkboxes have been created from Forms toolbar. However the feature of checkbox is such that the user can tick more than 1 checkbox. Is there a way that for each question only 1 checkbox is ticked? I attached a sample for your reference.
I have a range B1 to B4 for user to input value. Worksheet Change Event
However, the range B1 to B4 should not accept any value if the cell on the left hand side is empty.
Eg, If A1 is empty, B1 should not accept any value. If A2 is empty, B2 should not accept any value. If A3 is empty, B3 should not accept any value.
Below code checks for condition in A1 only.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B4")) Is Nothing Then
If IsEmpty(Range("A1")) Then MsgBox "Please input something under column A." With Application .EnableEvents = False .Undo .EnableEvents = True End With Target.Offset(0, -1).Select Exit Sub End If
End If
End Sub
Eg, If I enter something in A1, B2 would accept a value which is not what I hope to achieve. It should be checking for cell in A2 whther it is empty.
Would be grateful for some advice on how to modify the code.
I have 2 worksheets and I'd like the values in worksheet B to autopopulate depending on the values in worksheet A. For example, I'd like column K in worksheet B to add 5.50 to the integer value of column K of worksheet A. Some worksheet B columns will also have If, Then formulas like =IF(ISNUMBER(SEARCH("XXX",Cell#)),"YYY",""). How will that formula need to be revised to search another worksheet instead of within the same worksheet?
The starting sheet has lot of text cells with empty columns and rows between them, without pattern. I need to do the following:
-Copy the original sheet and work on the renamed copy -Remove all cells with bold font text (these are titles) -Create a new sheet -Transform the table to 1 column in the new sheet (no order required) -Remove empty rows -Save as CSV files with 2950 rows maximum each, with same name+number
I have attached a excel in this post. in that excel sheet, i need to take the input from the first sheet. The input contains special characters like : , ; , " .
I want to do the following operations.
1. replace all the special characters with line break
2. some email addresses prefix with To: . I need to remove "To:", "cc:", "1st support contact:"
3. after doing these operations , need to trim the column to remove unnecessary blank spaces.
4. remove the duplicates in the column
5. Sort them.
6. Need to remove some email addresses from the list. eg: "ocgsoy, Ggo", "IRFO"
I want the font color I have chosen for a cell in a worksheet to change the font color of a cell in a different worksheet inside the same workbook. Can someone help this newbie out?
I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.
Is it possible to store format of a sheet in a variable?
I have one sheet. i want to select all cell. then copy the formatting of this sheet(template) in a variable. is that possible? does variable need to be a clipboard? if it is in clipboard how to reference it? i.e if i have two or three formats stored in a clipboard how to select and paste the second one?
once i store this in a variable, i will paste in new sheets(about 50 of them) so that all these new sheets have same format as the template.
I'm using a sheet which provides a visual reminder to user via Conditional Formatting of which fields they need to complete, but there are cells outside of the 'User Input' range which i'd like to protect.
Any way by which I can still allow the Conditional Formatting to run, but protect the locked cells outisde of the input area.
