Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
There are four cells (merged cell ranges) I want users to
fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Phil,
Are you ready for this? This code goes in a normal code module, Module 1 '--------------------------------------------------------------------------- Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '--------------------------------------------------------------------------- This following code goes in the Scorecard worksheet code module '--------------------------------------------------------------------------- Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '--------------------------------------------------------------------------- and finally, this goes in the ThisWorkbook code m odule '--------------------------------------------------------------------------- Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '--------------------------------------------------------------------------- -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Bob, Thanks for your reply - you're right, I am stepping
into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil -----Original Message----- Phil, Are you ready for this? This code goes in a normal code module, Module 1 '--------------------------------------------------------- ------------------ Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '--------------------------------------------------------- ------------------ This following code goes in the Scorecard worksheet code module '--------------------------------------------------------- ------------------ Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '--------------------------------------------------------- ------------------ and finally, this goes in the ThisWorkbook code m odule '--------------------------------------------------------- ------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '--------------------------------------------------------- ------------------ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Phil,
Here it is. Just replace the CheckRange function with this code Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Or .Range(cell).Value <= 0 Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " must be entered, and must be 0" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " must be entered, and must be 0" End If CheckRange = sMsg & vbCrLf End If End With End Function -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... Bob, Thanks for your reply - you're right, I am stepping into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Bob, I receive a compile error: Type Mismatch,
Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") "G26" is highlighted -----Original Message----- Bob, Thanks for your reply - you're right, I am stepping into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil -----Original Message----- Phil, Are you ready for this? This code goes in a normal code module, Module 1 '-------------------------------------------------------- - ------------------ Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '-------------------------------------------------------- - ------------------ This following code goes in the Scorecard worksheet code module '-------------------------------------------------------- - ------------------ Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '-------------------------------------------------------- - ------------------ and finally, this goes in the ThisWorkbook code m odule '-------------------------------------------------------- - ------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '-------------------------------------------------------- - ------------------ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Phil,
Is this with the original or revised version? Although both should be okay, I have tried them both with and without merged cells. Want to send me the workbook to look at (do the usual with the email address) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... Bob, I receive a compile error: Type Mismatch, Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") "G26" is highlighted -----Original Message----- Bob, Thanks for your reply - you're right, I am stepping into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil -----Original Message----- Phil, Are you ready for this? This code goes in a normal code module, Module 1 '-------------------------------------------------------- - ------------------ Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '-------------------------------------------------------- - ------------------ This following code goes in the Scorecard worksheet code module '-------------------------------------------------------- - ------------------ Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '-------------------------------------------------------- - ------------------ and finally, this goes in the ThisWorkbook code m odule '-------------------------------------------------------- - ------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '-------------------------------------------------------- - ------------------ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
Bob, need your e-mail address
Bob Phillips wrote: *Phil, Is this with the original or revised version? Although both should be okay, I have tried them both with and without merged cells. Want to send me the workbook to look at (do the usual with the email address) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... Bob, I receive a compile error: Type Mismatch, Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") "G26" is highlighted -----Original Message----- Bob, Thanks for your reply - you're right, I am stepping into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil -----Original Message----- Phil, Are you ready for this? This code goes in a normal code module, Module 1 '-------------------------------------------------------- - ------------------ Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '-------------------------------------------------------- - ------------------ This following code goes in the Scorecard worksheet code module '-------------------------------------------------------- - ------------------ Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '-------------------------------------------------------- - ------------------ and finally, this goes in the ThisWorkbook code m odule '-------------------------------------------------------- - ------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '-------------------------------------------------------- - ------------------ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? . . * --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Requiring Cells be Populated
bob . phillips @ tiscali . co . uk
remove the spaces -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "pjhageman " wrote in message ... Bob, need your e-mail address Bob Phillips wrote: *Phil, Is this with the original or revised version? Although both should be okay, I have tried them both with and without merged cells. Want to send me the workbook to look at (do the usual with the email address) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... Bob, I receive a compile error: Type Mismatch, Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") "G26" is highlighted -----Original Message----- Bob, Thanks for your reply - you're right, I am stepping into the deep end. A request: I now have an additional requirement that not only must the cells be populated, the values entered must be greater than zero. Could you add that feature into the code? Thanks, Phil -----Original Message----- Phil, Are you ready for this? This code goes in a normal code module, Module 1 '-------------------------------------------------------- - ------------------ Public Function ValidScorecard() As Boolean Dim sMsg As String ValidScorecard = True sMsg = sMsg & CheckRange("G26") sMsg = sMsg & CheckRange("AG26") sMsg = sMsg & CheckRange("G44") sMsg = sMsg & CheckRange("AG44") If Len(sMsg) 0 Then MsgBox sMsg ValidScorecard = False End If End Function Private Function CheckRange(cell As String) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(cell)) Then If .Range(cell).MergeArea.Address(False, False) < cell Then sMsg = "Weight for cell(s) " & _ .Range(cell).MergeArea.Address & _ " is required" Else sMsg = "Weight for cell " & _ .Range(cell).Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End With End Function '-------------------------------------------------------- - ------------------ This following code goes in the Scorecard worksheet code module '-------------------------------------------------------- - ------------------ Private Sub Worksheet_Deactivate() If Not ValidScorecard Then Worksheets("Scorecard").Activate End If End Sub '-------------------------------------------------------- - ------------------ and finally, this goes in the ThisWorkbook code m odule '-------------------------------------------------------- - ------------------ Private Sub Workbook_BeforeClose(Cancel As Boolean) If ActiveSheet.Name = "Scorecard" Then If Not ValidScorecard Then Cancel = True Worksheets("Scorecard").Activate End If End If End Sub '-------------------------------------------------------- - ------------------ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... There are four cells (merged cell ranges) I want users to fill data into before leaving the worksheet (worksheet name is "Scorecard"); G26:I26, AG26:AI26, G44:I44, and AG44:AI44. I have an Auto_Open sub in place to open the workbook on this "Scorecard" worksheet. These four cells are weighting factors and the numbers, formatted percent, must equal 100%. Also, there must be a number entry in each of the four cells, even if it is zero. If the user does not make an entry (blank cell) a message must come up saying "Weight for cell *** is required." If they leave multiple cells blank, the message would include those cells as well. Users cannot leave the worksheet until all four cells are populated. What would the code be? Would I put the code in the worksheet code object, or in Module 1? . . * --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Requiring multiple cells to be populated before allowing Save | Excel Worksheet Functions | |||
copy to new cells if populated | Excel Discussion (Misc queries) | |||
Cells to be populated from an workbook to another. | Excel Discussion (Misc queries) | |||
Requiring data in certain cells | Excel Worksheet Functions | |||
how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) |