Highlight missing or duplicated cells

[Paul_Hossler]

Hi, I marked it as solved. But the new iteration does not reset the Dupe's from the Pink once fixed...

Originally started as

http://www.vbaexpress.com/forum/show...hat-is-entered

but as scope grew, a new thread is more appropriate

BTAIM, I mixed my Start and End variables in changing v25 , so v26 should be back on track

Also since the legend is now part of the MDS worksheet, I commented out the Userform .Show on WS_activate since it was getting annoying

[pawcoyote]

see below sorry

[pawcoyote]

Sorry Paul, but now the Protection doesn't work with version 26... Everything else does..

Originally started as

http://www.vbaexpress.com/forum/show...hat-is-entered

but as scope grew, a new thread is more appropriate

BTAIM, I mixed my Start and End variables in changing v25 , so v26 should be back on track

Also since the legend is now part of the MDS worksheet, I commented out the Userform .Show on WS_activate since it was getting annoying

[Paul_Hossler]

Did you run the 'ProtectMDS' macro?

Capture.JPG

That and the 'UnprotectMDS' macro have to be run manually so that you can edit the format when needed and then re-protect it for users

If there is still a problem, provide details and example and I'll look again

[pawcoyote]

I missed that at first. Now CA5 and CA6 to the end are still locked..

Range("CA5").Resize(2, 16).Locked = False '<<<<<<<<<<<<<<<<<<<

[Paul_Hossler]

Using v26 from the earlier post and running ProtectMDS, CA5:CL6 are editable for me

Am I misunderstanding?

Do you want them locked?

Be advised that if you change the column header text, the macro needs to be updated since the column header text is used to locate the appropriate column to copy data, etc.

[pawcoyote]

Hi, I have not changed the headers... I want them Unlocked they are locked.. CA5 and CL6 should (were) unlocked but they are locked now. I am trying to understand how they lock and unlock with the code. I am using v26. Thank you for helping me understand this.

Using v26 from the earlier post and running ProtectMDS, CA5:CL6 are editable for me

Am I misunderstanding?

Do you want them locked?

Be advised that if you change the column header text, the macro needs to be updated since the column header text is used to locate the appropriate column to copy data, etc.

[Paul_Hossler]

That's not what I'm seeing

I took v26 you posted and ran ProtectMDS macro

I can edit CA5


Capture.JPG

[snb]

Why no using Listobject ?
Why not designing the database properly (starting in A1 e.g.) ?
Why not using a userform ?
Refrain from using merged cells.

clumsy coding:

Sub Return_CoverPage()
'
' Return to Cover Page Macro
'
'
    Sheets("Cover Page").Select
    Sheets("Cover Page").Visible = True
    Range("C6").Select
End Sub

Sub Return_CoverPage()
    Sheets("Cover Page").Visible = True
    application.goto Sheets("Cover Page").cells(6,3)
End Sub

clumsy coding:

Sub MDSEquipmentDetail()
'
'   MDS Equipment Detail Macro
'
'
    Sheets("Cover Page").Select
    Sheets("MDS Equipment Detail").Visible = True
    Sheets("Cover Page").Select
    Sheets("MDS Equipment Detail").Select
    Range("B7").Select
End Sub

should be:

Sub MDSEquipmentDetail()
    Sheets("MDS Equipment Detail").Visible = True
    application.goto Sheets("MDS Equipment Detail").cells(7,2)
End Sub

[pawcoyote]

Thank you Paul I will check it out.. Maybe it got fubar on a download.

[pawcoyote]

Hi Paul, I just noticed I can not longer use CTRL Z or CTRL Y. It says Can't do it.. I need to be able to do that just incase I make a mistake and delete or change something I didn't want.

That's not what I'm seeing

I took v26 you posted and ran ProtectMDS macro

I can edit CA5


Capture.JPG

[mdmackillop]

You cannot use Ctrl+Z to undo a macro operation; just one of those things. You can build in an "Are you sure?", but they are a bit of an irritant.

[pawcoyote]

Its not a macro operation, the fields are suppose to be unprotected and should be able to do a Ctrl Z or Ctrl Y. I am not able to do either when typing in anything...

[Paul_Hossler]

The fields are unprotected, but the macro Worksheet_Change runs the macro ApplyChecks.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call ApplyChecks
End Sub

[pawcoyote]

So SOL on this, cannot get Undo or Redo with Macro's running.. I will have to think up something that allows for something like that... Weird how it doesn't allow for Undo...

[pawcoyote]

Hi, I came across an little issue when I added in a Clear Sheet button/Macro on the MDS Equipment Detail sheet.

Once I have used the Macro to clear the sheet and if by chance I click on the "Copy Info to MOST" button it throws a debug error and takes me to the below line in VB.

Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)

I would like to clear the contents of the sheet and not mess up any of the formatting that you helped with and the macros that pull in data. I would also like to just get an Msg Box stating there is no data to copy if the MDS worksheet is blank.

I noticed when I used the Clear.contents it also stopped the Region and Site Reference from updating as well.

Below is the clear contents that I used.

Sub Clear_MDS()'
' Clear Data from MDS Equipment Detail Worksheet
'
'
Range("B7:BU5001").ClearContents


End Sub

The fields are unprotected, but the macro Worksheet_Change runs the macro ApplyChecks.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call ApplyChecks
End Sub

[Paul_Hossler]

1. I added this on the Copy

    
    With wsMDS
        rowDataEndMDS = .Cells(.Rows.Count, iCol).End(xlUp).Row
    End With

    'added 3/21/2017
    If rowDataEndMDS < 7 Then
        MsgBox "No Data on MDS to copy to MOST"
        Exit Sub
    End If
    
    Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)

2. Your Clear sub did not specify a worksheet, so as is it would work on the active sheet. Since you had it tied to a button on the desired sheet, it would work if called from the button.

I added two flavors of 'Clear to a new module 'mod_Clear'

Option Explicit


'added 3/21/2017
' Clear Data from MDS Equipment Detail Worksheet
Sub Clear_MDS()

    Set wsMDS = Worksheets("MDS Equipment Detail")
    
    If MsgBox("Are you SURE you want to clear all the data on " & wsMDS.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMDS.Name) = vbNo Then Exit Sub

    Application.EnableEvents = False
    
    UnProtectMDS
    
    With wsMDS
        Range(.Rows(rowDataStartMDS), .Rows(.UsedRange.Rows.Count)).Clear
    End With
    
    ProtectMDS
    
    Application.EnableEvents = True
End Sub


'added 3/21/2017
' Clear Data from MOST Equipment Add Worksheet
Sub Clear_MOST()
    Set wsMOST = Worksheets("MOST Equipment Add")
    
    If MsgBox("Are you SURE you want to clear all the data on " & wsMOST.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMOST.Name) = vbNo Then Exit Sub
    
    With wsMOST
        Range(.Rows(rowDataStartMOST), .Rows(.UsedRange.Rows.Count)).Clear
    End With
End Sub

Check it out and let me know

[pawcoyote]

Thank you got it on the Clear... and Thank you on the Copy...

[pawcoyote]

Hi,

I tried to add another clear and I get a error.. I have another sheet called "Worksheet" and for some reason it doesn't like the wsWorksheet = Worksheets Its giving me a compile error, saying I didn't define the variable.

One other funky thing is once I use the Clear MDS Sheet and try to go an enter data into the sections I get a Debug error. Runtime Error "1004" And it takes me to to this line in mod_MDS " Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)"

It seems to break all the other code in the MDS sheets cells when we do the Clear sheet. Once that is pressed and used I get the run time error and all the formatting is removed and I no longer get any of the other code running.

Option Explicit

'added 3/21/2017
' Clear Data from MDS Equipment Detail Worksheet
Sub Clear_MDS()




Set wsMDS = Worksheets("MDS Equipment Detail")

If MsgBox("Are you SURE you want to clear all the data on " & wsMDS.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMDS.Name) = vbNo Then Exit Sub


Application.EnableEvents = False

UnProtectMDS

With wsMDS
Range(.Rows(rowDataStartMDS), .Rows(.UsedRange.Rows.Count)).Clear
End With

ProtectMDS

Application.EnableEvents = True
End Sub


'added 3/21/2017
' Clear Data from MOST Equipment Add Worksheet
Sub Clear_MOST()


Set wsMOST = Worksheets("MOST Equipment Add")

If MsgBox("Are you SURE you want to clear all the data on " & wsMOST.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsMOST.Name) = vbNo Then Exit Sub

With wsMOST
Range(.Rows(rowDataStartMOST), .Rows(.UsedRange.Rows.Count)).Clear
End With


End Sub
'added 3/21/2017
' Clear Data from Worksheet
Sub Clear_Worksheet()


Set wsWorksheet = Worksheets("Worksheet")

If MsgBox("Are you SURE you want to clear all the data on " & wsWorksheet.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsWorksheet.Name) = vbNo Then Exit Sub

With wsWorksheet
Range(.Rows(rowDataStartMOST), .Rows(.UsedRange.Rows.Count)).Clear
End With


End Sub

[Paul_Hossler]

Not sure I followed that

1. I wouldn't use 'Worksheet' as a worksheet name

2. Did you Dim wsWorksheet? wsMOST and wsMDS are Dim-ed on mod_Globals

3. Your Clear_Worksheet uses rowDataStartMOST which is a constant to define the starting data row on the MDS worksheet. It's probably not right for worksheet 'Worksheet'

Post a sample WB containing 'Worksheet' and I'll look at that and this

One other funky thing is once I use the Clear MDS Sheet and try to go an enter data into the sections I get a Debug error. Runtime Error "1004" And it takes me to to this line in mod_MDS " Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)"