PDA

View Full Version : Personal.xlsb ..... or ?



stuartgb100
04-25-2015, 09:57 AM
Hi,

I pass workbooks to colleagues for them to work on and return to me.

I wish to find a way of helping a user with a visual check that they are happy they have finished their data input, and if not, to remind them of the fact.

Their input results in totals being placed in col K, with a final total at the end of the range in K.

Take the range K6:K499.

Some, but not all of the rows between 6 and 499 require user data.
where it does, the data they enter results in a sumproduct in the corresponding K cell.
The total is dynamically returned in K500.

If a user misses data input, then K500 is incorrect.
Their data input is quite complex, so they might enter 4 parts of data, leaving out the fifth, meaning to return to that dataset later. But if they forget, there will be a problem.

So I'm thinking as follows:

I pass the workbook to a colleague, having turned the cell fill colour to red in the range K6:K499

I put code in their machine's personal.xlsb which places a button on the toolbar.
This button is available to all workbooks/sheets that they open.
User opens their workbook and selects the sheet, then clicks the button.

The code then does as follows:

startcell = k6; endofrange = k499; lastcell = k500

1. establish the range being worked on in Col K (lastcell will be the first cell found sampling up col K that has a top border set); first cell range k6.
2. test the range (k6 and lastcell-1)
3. if any cell in the range has a fill colour value > 0 (ie there are red cells)
Then .rng lastcell.value= "ERROR"
Else .rng.value=sum(K6:K499)

So as long as at least one cell in col K is red, there'll be no total, simply ERROR, prompting user to check their data input.

Two further questions, please:

1.The user is free to input data rows and to delete (it is their data after all).
This means they can either delete or insert rows at will.
Will this cause a problem ?
2. How best to fire the procedure - an Event Change in col K ?

Regards and thanks,
Stuart.

stuartgb100
04-26-2015, 01:25 AM
I have written some 'pseudo code' and list it below.
I'd appreciate comment, please:

OptionExplicit
Sub Check_All_Items_Are_Priced()
Dim C AsRange, Ws As Worksheet, StartRow As Long, EndRow As Long, TotalCell As Long,Rng As Range
‘This routine will checkthat the User is happy all items are fully-priced.
‘Before starting to priceany items, all the rows to be priced need to have
‘the cell fill-colour incol K set to a colour (any colour or combination of
‘colours will do). Theroutine will check col K and if any cell has a fill-colour
‘other than clear, it willcolour the Total cell in red and insert the value
‘ ERROR. If all is good,it will put a sum formula into the Total Cell and set
‘the fill-colour to clear.

With ActiveWorksheet
.Unprotect
ForEach C In .Range("K6", .Range("K65536").End(xlUp))
‘find the first cell from the bottom that has its topborder set
IfC.xlbordertop is true Then
SetTotalCell = C
EndIf
Next
‘set the range, which is K6:TotalCell row number - 1
Set Rng = .Range (“K6:TotalCell-1”)
For Each C In .Rng
‘checkif any cell in the range has any fill colour other than clear
If C.FillColour.Value > 0Then
MsgBox “ Youstill have items to complete”
.TotalCell.FillColour.Value= “RED”
.TotalCell.Value= ERROR
Exit Sub
End If
Next
‘ If we’vereached here, then all the cells should have no fill colour
‘so setthe sum total
Set TotalCell.FillColour.Value= “CLEAR”
Set TotalCell.Value =Sum(K6 : TotalCell-1)
End With

gmayor
04-26-2015, 07:41 AM
If you add the following code to the ThisWorkbook module of the worksheet (and save it as macro enabled), then provided the users will allow the macro to run, when you open the workbook, the range K6 to K500 is coloured red. If you change any value in any cell in this range, the colour is removed. If you try and close the workbook when one or more of the cells is red you will see a warning and the book will not close.



Option Explicit

Private Sub Workbook_Open()
Sheets(1).Range("K6", "K500").Interior.ColorIndex = 3
lbl_Exit:
Exit Sub
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 11 Then
If Target.Row > 5 And Target.Row < 500 Then
Target.Interior.ColorIndex = xlColorIndexNone
End If
End If
lbl_Exit:
Exit Sub
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As Long
For i = 6 To 500
If Sheets(1).Range("K" & i).Interior.ColorIndex = 3 Then
MsgBox "You must complete all the red coloured cells."
Cancel = True
Exit For
End If
Next i
lbl_Exit:
Exit Sub
End Sub

stuartgb100
04-26-2015, 10:58 AM
Thanks Graham.

I think I can make this work.

One question please:

If I open user's workbook and change some of the data (and turn red cells off)
but not be able to finish all of the red cells

Can I save the workbook (with my changes) and come back to it later to finish ?

Thanks again.

gmayor
04-26-2015, 09:43 PM
You can save the workbook, but you cannot close it if there are red cells in the range, and when you re-open it all the cells in the range are turned red again.

snb
04-27-2015, 02:06 AM
If you want to offer any 'guidance' to users checking their input you should use userforms.