PDA

View Full Version : Highlight missing or duplicated cells



Paul_Hossler
02-26-2017, 11:24 AM
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/showthread.php?58303-VBA-Code-to-Match-US-Region-to-the-State-that-is-entered

but as scope grew, a new thread is more appropriate

BTAIM, I mixed my Start and End variables in changing v25 :doh:, 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:thumb

pawcoyote
02-28-2017, 02:45 PM
see below sorry

pawcoyote
02-28-2017, 02:52 PM
Sorry Paul, but now the Protection doesn't work with version 26... Everything else does..
Originally started as

http://www.vbaexpress.com/forum/showthread.php?58303-VBA-Code-to-Match-US-Region-to-the-State-that-is-entered

but as scope grew, a new thread is more appropriate

BTAIM, I mixed my Start and End variables in changing v25 :doh:, 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:thumb

Paul_Hossler
02-28-2017, 03:12 PM
Did you run the 'ProtectMDS' macro?

18519

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
02-28-2017, 04:46 PM
I missed that at first. Now CA5 and CA6 to the end are still locked..


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

Paul_Hossler
02-28-2017, 05:41 PM
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
02-28-2017, 08:24 PM
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
02-28-2017, 08:44 PM
That's not what I'm seeing

I took v26 you posted and ran ProtectMDS macro

I can edit CA5


18522

snb
03-01-2017, 01:17 AM
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
03-01-2017, 06:54 AM
Thank you Paul I will check it out.. Maybe it got fubar on a download.

pawcoyote
03-17-2017, 01:41 PM
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


18522

mdmackillop
03-17-2017, 02:08 PM
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
03-17-2017, 03:37 PM
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
03-18-2017, 12:37 PM
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
03-18-2017, 12:57 PM
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
03-21-2017, 10:13 AM
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
03-21-2017, 11:25 AM
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
03-21-2017, 03:43 PM
Thank you got it on the Clear... and Thank you on the Copy...

pawcoyote
03-21-2017, 04:13 PM
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
03-21-2017, 07:08 PM
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)"

Paul_Hossler
03-21-2017, 07:24 PM
I changed the MDS formatting / checking logic.

The checks don't run until there's an enter in Client (Col B), but the debug message seems to go away

Client Name (Col B) is used that way because you had line numbers in Col that went past real data

pawcoyote
03-22-2017, 05:47 AM
Roger that on Worksheet, I will post up a new WB. I will check to be sure I did the DIM.

On the MDS sheet when I use the Clear Macro, it clears all formatting and if I try to use the fields right after that I get the Runtime Error. Right after I press the button and it clears I go to field B6 and enter something and go to the next cell it give the debug. It also stops all the other macros built in from running. No color coding, no Region etc... Nothing works after the clear...

pawcoyote
03-22-2017, 06:41 AM
Good morning,

I was able to fix the Worksheet Clear. I changed the name of the sheet and I did miss things in the mod_Global. I made notes in there.

My big issue now is that when I use the Clear MDS Sheet it messes everything up in the area I can add info into. I get the Debug error. Once I click the Clear MDS Data it works good. But when I go into the area I can enter data starting at A7, I type a 1 in there and hit enter or try to go to another cell it gives the debug error. I also then loose all other functions in the cells i.e. Color Coding the dupes, required fields, the auto fill of Region and Site reference. I left and entered back into the sheet and still nothing works. I have attached Sample 30 and two screen shots with the error.

1871518716






I changed the MDS formatting / checking logic.

The checks don't run until there's an enter in Client (Col B), but the debug message seems to go away

Client Name (Col B) is used that way because you had line numbers in Col that went past real data

Paul_Hossler
03-22-2017, 07:19 AM
1. I noticed that your WS 'Names' seems to have lists of acceptable values. You can add Data Validation rules to allow picking from a list. I did 'State' and 'Device Type' as examples. You can add more calls to pvtAddValidation for the rest of the fields

18725



Option Explicit
Sub AddValidationRules()
Init
Set headerNames = wsNames.Rows(rowHeaderNames)
UnProtectMDS
Call pvtAddValidation("Location State", "State", "State", "Pick State from list")
Call pvtAddValidation("Device Type", "DeviceType") ' Note - no space
ProtectMDS
End Sub


Private Sub pvtAddValidation(DataColumn As String, ListColumn As String, Optional Title As String = "Allowed Values", Optional Msg As String = "Select entry from the list")
Dim r As Range
Dim colMDS As Long, colNames As Long
colMDS = GetColumnNumber(DataColumn, headerMDS)
colNames = GetColumnNumber(ListColumn, headerNames)

Set r = wsNames.Cells(2, colNames)
Set r = Range(r, r.End(xlDown))

With wsMDS.Cells(rowDataStartMDS, colMDS).Resize(1000, 1).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & wsNames.Name & "'!" & r.Address(True, True)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = Title
.ErrorTitle = vbNullString
.InputMessage = Msg
.ErrorMessage = vbNullString
.ShowInput = True
.ShowError = True
End With

End Sub




2. I added a call to AddValidationRules to Clear_MDS to add the State at least as a DV rule

3. Region updates from State now (BTW, the 'Region' has 'Filed' instead of 'Field')

18726

Paul_Hossler
03-22-2017, 07:24 AM
Good morning,

My big issue now is that when I use the Clear MDS Sheet it messes everything up in the area I can add info into. I get the Debug error. Once I click the Clear MDS Data it works good. But when I go into the area I can enter data starting at A7, I type a 1 in there and hit enter or try to go to another cell it gives the debug error. I also then loose all other functions in the cells i.e. Color Coding the dupes, required fields, the auto fill of Region and Site reference. I left and entered back into the sheet and still nothing works. I have attached Sample 30 and two screen shots with the error.




That should have been corrected in v29 on Post 21 by adding some 'no data' logic (below). I did not see the added logic on your highlighted debug message




' -------------------------------------------- state
If rowDataEndMDS - rowDataStartMDS + 1 = 0 Then
Set rMDS = wsMDS.Rows(rowDataStartMDS)
Else
Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)
End If



Try the ver 30 on post 24 and see

pawcoyote
03-22-2017, 09:51 AM
Thank you I will look them both over.. I truly appreciate all your assistance.

pawcoyote
03-22-2017, 10:51 AM
Would it be hard on the MDS Sheet to Start Clearing from Column B Row 7. Leaving Column A alone? Or would that mess up everything you have done.
That should have been corrected in v29 on Post 21 by adding some 'no data' logic (below). I did not see the added logic on your highlighted debug message




' -------------------------------------------- state
If rowDataEndMDS - rowDataStartMDS + 1 = 0 Then
Set rMDS = wsMDS.Rows(rowDataStartMDS)
Else
Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1)
End If



Try the ver 30 on post 24 and see

Paul_Hossler
03-22-2017, 11:22 AM
Would it be hard on the MDS Sheet to Start Clearing from Column B Row 7. Leaving Column A alone? Or would that mess up everything you have done.


This is Excel and VBA ... NOTHING is impossible:rotlaugh:

Well, maybe not everything

Look at the last ver 30 (I forgot to bump the version suffix I guess) and I'll at this and your feedback into ver 31

pawcoyote
03-22-2017, 12:00 PM
Hi, that is cool.. Do you want a copy of the most up to date version I have? I am attaching it here version 33... I am still getting the error when I click the Clear MDS Data .. Maybe I didn't write the code right?


This is Excel and VBA ... NOTHING is impossible:rotlaugh:

Well, maybe not everything

Look at the last ver 30 (I forgot to bump the version suffix I guess) and I'll at this and your feedback into ver 31

pawcoyote
03-22-2017, 03:16 PM
I fixed my DataValidations with the Named Ranges. I also have the complete workbook up to date with every sheet. I am still getting the errors when I try to Clear the MDS Equipment Sheet.

Paul_Hossler
03-22-2017, 04:00 PM
It looks like a change was not in your 33a, so I put it in 34

pawcoyote
03-23-2017, 07:07 AM
Not sure what you changed but that worked. But, the State to Region and Build of the Site Reference stopped working. I also added in the .ClearContent of the PreMDS Sheet, which works.


It looks like a change was not in your 33a, so I put it in 34

Paul_Hossler
03-23-2017, 08:28 AM
The State-Region used to be in J:K on Names, now it's in L:N

18741

So when looking up a State, no entry was found

That also affected 'Oracle Site Reference'

pawcoyote
03-23-2017, 09:53 AM
I redid the Names Sheet to make it easier for viewing and finding things and I lined them up with how the names flow on the MDS sheet. Where would I fix them if I add items into the Names sheet or move things?

I went to the mod_MDS and Changed the line to be "Set rStatesRegions = Worksheets("Names").Range("L:N")" But It keeps showing M (Floor) associated to the State. It works on your sheet but I would like to see where I make the changes to make this work.. Are there other locations I need to update?

The Site Reference is coming in Correctly.

pawcoyote
03-23-2017, 11:10 AM
I was able to get it to work by just copying your mod_MDS to mine but I would love to know where changes happen for my edification!

Paul_Hossler
03-23-2017, 11:16 AM
I redid the Names Sheet to make it easier for viewing and finding things and I lined them up with how the names flow on the MDS sheet. Where would I fix them if I add items into the Names sheet or move things?

I went to the mod_MDS and Changed the line to be "Set rStatesRegions = Worksheets("Names").Range("L:N")" But It keeps showing M (Floor) associated to the State. It works on your sheet but I would like to see where I make the changes to make this work.. Are there other locations I need to update?

The Site Reference is coming in Correctly.


In the associated VLookup, change the 2 to 3 since before using J:K the region was in the second column of the Range, but now using L:N region is in the third

pawcoyote
03-23-2017, 11:23 AM
Roger thank you, one more question.. In the Data Validation fields can I have a auto complete or search type feature? i.e. I start to Type MFD and as I type it starts to fill in or allow me to select from the list.. It would be neat to be able to do that without having to scroll through the listings... Just a thought..
In the associated VLookup, change the 2 to 3 since before using J:K the region was in the second column of the Range, but now using L:N region is in the third

Paul_Hossler
03-23-2017, 01:34 PM
Roger thank you, one more question.. In the Data Validation fields can I have a auto complete or search type feature? i.e. I start to Type MFD and as I type it starts to fill in or allow me to select from the list.. It would be neat to be able to do that without having to scroll through the listings... Just a thought..

No way to do that I'm aware of

Sorry

pawcoyote
03-23-2017, 01:48 PM
Roger that... I will be starting a new thread soon. This one is complete. I will be using the same workbook but different criteria.

pawcoyote
03-24-2017, 06:56 AM
Quick question for you.. When copying from the MDS to the MOST on the Ship to Address it is dropping the Lead Zero for the Zip Code is there a way to fix that?
No way to do that I'm aware of

Sorry

Paul_Hossler
03-24-2017, 08:00 AM
ClearMOST was off by one row. It left the first row of data




Public Const rowHeaderMOST As Long = 4 'MOST Sheet Header Start Row
Public Const rowDataStartMOST As Long = 5 'MOST Sheet Data Start Row




In BuildMOST, change the ZipCode part from .Value to .Text (right at the end of these two)




.Cells(10).Value = LUV(headerMDS, "Location Street Address", i).Value & " " & LUV(headerMDS, "Location City", i).Value & " " & LUV(headerMDS, "Location State", i).Value & " " & LUV(headerMDS, "Location Zip", i).Text

....

.Cells(43).Value = LUV(headerMDS, "Location Street Address", i).Value & " " & LUV(headerMDS, "Location City", i).Value & " " & LUV(headerMDS, "Location State", i).Value & " " & LUV(headerMDS, "Location Zip", i).Text

pawcoyote
03-24-2017, 08:12 AM
That is what I did and thank you...