Sub NewEntry() 'goes to the first empty cell at the end of a validation list Sheets("Validation Lists").Select Range("First_List").End(xlDown).Select ActiveCell.Offset(1, 0).Activate 'enters InputBox data ActiveCell.Value = InputBox("What is the New Entry?") 'THE LINE ABOVE DOESN'T WORK End SubWhat is my problem?
Private Sub PrintWorksheet_Click() 'Quick print of active worksheet to default printer upon PrintWorksheet 'cmd button click. No printer dialog box. Call BlankCheck ActiveSheet.PrintOut End SubAnd this is the macro that checks for blank cells:
Sub BlankCheck() Dim blnEmptyCell As Boolean blnEmptyCell = False If Sheets("Sheet1").Range("B13") = "" Then blnEmptyCell = True If Sheets("Sheet1").Range("B14") = "" Then blnEmptyCell = True If blnEmptyCell = True Then MsgBox "One or more of the required fields is blank. Please complete all required fields." Exit Sub Cancel = True End SubThe problem I have is that the second macro keeps checking cells, and displaying error messages each time it find a blank. I have a lot more cells to check, 36 total. Also, is there a cleaner way to structure the macro so I'm not checking one cell per command line?
With wbBook Set wsCountries = .Worksheets("Countries") Set wsErrors = .Worksheets("ERRORS CHECK") End With '.......' With wbBook Set Selez = wsErrors.Cells(k, j) wsErrors.Hyperlinks.Add Selez, " #' " & wsCountries.Name & " ' ", subaddress= "cell(i, dc)" End WithSo:
is the cell with the WARNING in which I want to add the Hyperlink....
2) When people click on the hyperlink: the destination will be the worksheet wsCountries and the cell will be the one in which the input-error was found:where i and dc will have the value of the row and the column where the error was found.
The number of hyperlinks created, is proportional to the number of errors found by the program while "checking" the database COUNTRIES.
Concluding: instead of using an explicit address, like...
...I need to replace A2 with the dynamic Variables i and dc.
Does anyone knows how to write the syntax for this?
Thank you very much in advance, I hope it was clear!
Now instead of clearing the two cells i want them to be populated with data in a different column along the same row.
If the Status cell is changed then i want the "Date" column to be todays date. Also, if the status cell value changes i want the "Reviewer's Intials" to be set equal to the value in the "Owners" column.
Any idea on how to do this? I tried a few things and you can see what i tried in teh vba code on the "Sheet4" section.
I am enclosing a file as an example.
VB:While I realize the above code won't actually work, IS there a way to use For Each with the Cells collection?Dim celX As Cell ' I realze that Excel's VBA doesn't actually have a Cell object ' Iterate Through rngMyRange's Cells Collection For Each celX In rngMyRange.Cells MsgBox(celX.Value) Next celXIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Using a For... Each would be easier, but is there a way to do it?Sub ForLoopCell() Dim iCntr As Integer, rngTest As Range Set rngTest = ActiveSheet.Range("B1:B10") ' Iterate Through Cells Collection By Using A Counter For iCntr = 1 To rngTest.Cells.Count MsgBox (rngTest.Cells(iCntr).Value) Next iCntr End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines