Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalling named ranges as must fill cell
on my workbook I have some ranges that must be filled in prior to
saving the file: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim checkCells As Range Dim cell As Range Set checkCells = Sheets("Sheet1").Range("A1, B2, C3") For Each cell In checkCells Cancel = Cancel Or IsEmpty(cell) Next cell If Cancel Then MsgBox "Cell(s) " & Replace(checkCells.Address( _ False, False), ",", ", ") & _ " must be filled in prior to saving." End If End Sub What I want to acieve is this A1 cosrresponds to Name and B2 corresponds to Address I would like the user to have a flag that sais you must fill in Name, Address etc and the cells are a different colour on the spreadsheet. id it possible? thanks Alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalling named ranges as must fill cell
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean) Dim checkCells As Range Dim cell As Range Dim msg As String msg = "Cell(s) " Set checkCells = Sheets("Sheet1").Range("A1, B2, C3") For Each cell In checkCells If IsEmpty(cell.Value) Then msg = msg & cell.Address(False, False) & vbNewLine Cancel = True End If Next cell If Cancel Then MsgBox msg & " must be filled in prior to saving." End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ... on my workbook I have some ranges that must be filled in prior to saving the file: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim checkCells As Range Dim cell As Range Set checkCells = Sheets("Sheet1").Range("A1, B2, C3") For Each cell In checkCells Cancel = Cancel Or IsEmpty(cell) Next cell If Cancel Then MsgBox "Cell(s) " & Replace(checkCells.Address( _ False, False), ",", ", ") & _ " must be filled in prior to saving." End If End Sub What I want to acieve is this A1 cosrresponds to Name and B2 corresponds to Address I would like the user to have a flag that sais you must fill in Name, Address etc and the cells are a different colour on the spreadsheet. id it possible? thanks Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
multi-cell named ranges | Excel Worksheet Functions | |||
Named Ranges - accessing a cell | Excel Discussion (Misc queries) | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Named Cell Ranges | Excel Discussion (Misc queries) |