I have a range Named "MyRange" which consists of cells "A4:H20". What I am attempting to do is when the user goes to save and or close the workbook it checks that all the cells within the range have been filled with data.
View 3 RepliesI'm looking for a way to ensure that users of a spreadsheet have filled in all required cells. VBA code which will prompt if a cell is blank which will activate on a button click
All cells are 'Named ranges' so hoping there's a way in which I can point to all named ranges and if they're blank display the below error message.
"Please ensure you have filled in all required fields"
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
View 5 Replies View RelatedTrying to enter dates across first column with userform. Must not enter same date twice. Need to searching row to skip entering the date from userform textbox if date is already there. Dates are in order but not sequential. I want to search for existing date before the following
Set LastCol = Sheet2. Range("jk1").End(xlToLeft) 'enter data on sheet2
LastCol.Offset(0, 1).Value = TextBox2.Value 'date
Rows("1:1").Select
ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Add Key:=Range("b1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").sort
.SetRange Range("b1:jk10000")
. Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
I keep getting a "type mismatch error" for this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Len(Range("E18:E34")) = 0 Then
MsgBox "You must complete cell E18 thru E34 before you can save this file!"
Cencel = True
End If
I am trying to set a range of cells to ensure they are completed before the file can be saved. I can get it to work with individual cells, but not when I use multiple cells like in the example above.
This Works fine:
If Len(Range("D5")) = 0 Then
MsgBox "You must enter the Impress Amount before you can save this file!"
Cancel = True
End If
write this in VBA on the Workbook Level, "ThisWorkbook" : IF range (B20:B53, E20:E53, H20:H51) are all filled through user keypunched values--checks and coupon amounts.AND range (C20:C52, F20:F52, I20:I50) are equal to = " "
-- these ranges contain formula that spits out values only when there are discrepancies with the manually keypunched values above' otherwise it's equal to " ".THEN call batch02. batch02 is a macro that prints the specified batch.I have attached the filed I am working with. There are 25 batches, hopefully I can replicate the codes by just changing the ranges and the print macro.
I have a combobox on a spreadsheet it is populated on a sheet called teams :
The Format control has an input range of: Teams!$B:$B
Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.
I am using the copy range command to copy cells from one worksheet to one that is generated on commandbutton click. Is there any way to 'paste' the cells in row 3 instead of row A?
The code i am using looks like this -
Worksheets("Products").Range("A" & i + 2).Resize(1, 6).Copy Range("C" & Rows.Count).End(xlUp).Offset(1)
This code works fine, but i want the data to start in row 5, not at the top.
Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?
View 8 Replies View RelatedIve been trying to find out how i can paste information in the next available column but between certain columns. Here is an example of what im trying to do. There is a calculator which represents 3 machines. It runs different senerios if you change the % of product going into it (cells to change this are light blue). What im trying to do is take the information the machine outputs to the right and organize it on the next worksheet.
The data on the next worksheet is a combination of all the machines performance (in yellow) together and the machines personal performance (in red). when the button is pushed currently, I have the overall scenerios information filling down rows. What I would also like it to do is see if im using a machine (because it can be turned off by changing the usage to 0%), and if I am, copy the results form the individual machine to the next worksheet. When it copies it needs to see if the first machine slot is open and if not the second and paste in the available location. I cant have a specific spot for each machine on the real worksheet cause there are maybe 100 machines and if 15 are used I only want the first 15 machine info slots filled and im gonna hide the rest of the columns. The way the columns fill also cant be like the rows where it looks for next available free spot. It has to only be for the specific range in red because there is info to the right of where this is going.
Sub Send_Data()
Sheets("Calculator Sheet").Select
Range("AD9, AE9, AG9").Copy
Sheets("output sheet").Select
Range("B65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
I'm stuck using Excel 2003 to auto-populate a cell.
I have a range of dates in five consecutive columns called:
Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5
I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.
Each phase is consecutive to the next so will always be filled in from 1 to 5.
I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.
I've tried nesting some ISBLANK functions without any luck.
I am trying to write a script to import, reformat and save sales files for upload into our master server and had a question.
how to make it store a range as a dim to use later. I would use
Code:
Range(ActiveCell,ActiveCell.End(x1 Down)).Select
on all the columns, but there are often blanks in many of the rows and I am worried about potential errors.
There is a column which always has all of the rows populated, so I was hoping to could store the range of that column and use it for all the others, so they all match length.
Refining the below code which repeats itself for 60 agents per worksheet, for 7 worksheets. Is there a way to loop the code so it counts up 68 cell references until the 'blank cell' condition is met?
[code]'## Agent 1
If Sheets("CSR Dashboards").Range("M3").Value = "" Then sResult = MsgBox( _
Prompt:="CSR Dashboards have been sent.", _
Buttons:=vbOKCancel)
If sResult = vbOK Then
[Code] ........
Sub Testing()
Dim r, LR As Range
Dim k As String
Dim i, Total_Hours, Employee_Row, Last_Row As Integer
i = 0
Employee_Row = 5
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
i = i + 1
If i 1 Then
If k = "" Then
'First Line
Total_Hours = Range("J" & r.Row).Value
I'm saving LR.Range ("A" & r.Row - 1) into LR as I need to use it outside the loop for the last row here
Sheets("Sheet2").Range("B" & Employee_Row).End(xlUp).FormulaR1C1 = Range("A" & LR.Row).Value
It seems I cannot save a range like thateven using LR as an interger and doing LR = r.Row does not work.
Thanks for all the help so far with this issue. I seem to be getting errors after running a macro a second time. The name of the cell range is changing. I have attached the spreadsheet and I will try to be specific as possible.
Goal 1: I need to input monthly data into the Sales tab and then save it as a CSV file.
Goal 2: Re-open original xls file that has data then Run macro to create reports.
Goal 3: Save Original xls file as a clean sheet to use again next month.
Process:
1 - Insert Data into Sales tab
2 - File/Save As a CSV (Now the CSV file is open)
3 - Close CSV file and re-open the original xls file
4 - Run Macro to create reports
5 - Print Reports
6 - Manually delete current data on all tabs so I have a clean sheet for next month
7 - Save and Close
Problem:
When I open the xls file (next month) to input new data and run the reports I get a Run Time Error '1004" The Pivot Table field Name is Not Valid
I found that the Named Range of cells changed from the original:
I have previously tried to save the whole original workbook with the following
ActiveWorkbook.SaveAs Filename:="C:UsersPhilDocumentsMy Excel DocumentsRostersIndividual Rosters" & StaffLastName & " " & StaffFirstName & " - Roster commencing " & Format(DateSerial(Range("X11").Value, Range("V11").Value, Range("T11").Value), "dd mmm yyyy") & ".xlsm
but receive a run-time error message:
Run-time error '1004': Microsoft Office Excel cannot access the file
My first request for help is how is the code wrong and how can it be corrected?
Secondly, instead of saving the whole workbook, is it possible to save just a given range of cells as a new workbook, using the format above to give it a personalised filename?
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
My form has a combobox with three options "Withdrawal" "Deposit" "Fee". I want to make sure that whatever number a user puts into a textbox, if they select "Withdrawal" or "Fee" that number will be converted to a negative number, and if they select "Deposit" it will be positive. I have written the following code and am just wondering if there is some super slick way of doing it other than an if statement.
View 2 Replies View RelatedI am trying to ensure that only alpha numeric data (one letter then 4 digits) is entered in a cell.
View 9 Replies View RelatedI enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.
I have five text boxes on a user form. I would like the 5th box to always equal 100 - the Sum of the other boxes, and never go below 0.
In other words, the text boxes are representing percentage breakdowns - so to validate the percentage entry in each box i want the last box to 'count down' from 100 as the percentages are distributed amoungst the other boxes. This box will also be a percentage figure, so is crucial it has its own box (rather than just saying these four boxes have to total 100) i hope this makes sense!
I have tried a code along these lines (see below) - (adapted from this forum but couldnt get it to work) I also found a version where one poster used a command to change the 'value' from string to numbers - but have been unable to find that again.
Private Sub txtbox1.change()
txtbox5 = 100 - (CCur(txtbox1) + CCur(txtbox2) + CCur(txtbox3) + CCur(txtbox4))
End Sub
I have largish workbooks (10MB) with a variety of formulas and lookups to generate tables and charts. Calculation is ALWAYS set to Automatic, but sometimes (not always) the formulas fail to update when values are changed. Sometimes F9 will force calculation, sometimes Ctrl + Alt + F9, sometimes (especially with charts) I have to close the workbook and reopen before they will update. The workbooks contain macros but none are running when this happens. A search of your forum indicated that this question has arisen several times before, but I haven't seen a definitive answer.
View 6 Replies View RelatedI have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?
View 6 Replies View RelatedI have two enormous lists of dates. How do I automatically compare them to ensure the date on one comes after the date on the other one?
They're formatted as dates. Christmas Day 2000 (UK) did say 25/12/2000, and then when I reformatted it it automatically changed to 25 Dec 2000, and so on.
I have created a userform to add a new user and his/her password to a list of usernames and passwords (which i use for login procedure). Now, as the login name must be unique, I would like the userform to disallow existent usernames from being added. How do I go about doing this?
View 9 Replies View RelatedI have a workbook containing several sheets, each sheet has a large number of ActiveX check box controls on it.
The controls are presented in groups of three to capture responses to a question (Y/N/NA). If one of the three check boxes is set to True, the other two associated check boxes must be set to False.
What I want to do is avoid having to have an On_Click event sub for every single check box.
I have written a function that will handle updating the related check boxes but I am unsure how to call this function, passing it the name of the clicked Check Box whenever any check box is clicked.
Here is my current code with an On_Click event being used to call the function:
Private Sub chk100_01Y_Click()
' Want to replace this with a dynamic sub that will be invoked
' when any Check Box is clicked and pass the name of that Check
' box to the function
Call Update(ActiveSheet.OLEObjects("chk100_01Y"))
End Sub
it's been a long time I've posted here. I need some advice on how to make words blinking in excel. I have problem in sending proper instructions in my staff in my excel. So I think that blinking words will get my staff attention.I have search the forum but can't find any similar discussions.
View 5 Replies View Relatedmy formula is in B1. If in A1, there is letter Y, the value in B1 should accept only 3-digit numbers.
View 2 Replies View RelatedWhen a user inputs a month and then a day, I want to be able to check to make sure that the day entered is possible in that given month. Is there a way to do that which is not too complicated?
View 9 Replies View Related