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
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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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...
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.
Runtime-Error-032217.jpgRuntime-Error-032217-VBA Section.jpg
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
Capture4.JPG
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')
Capture5.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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
Try the ver 30 on post 24 and see' -------------------------------------------- state If rowDataEndMDS - rowDataStartMDS + 1 = 0 Then Set rMDS = wsMDS.Rows(rowDataStartMDS) Else Set rMDS = wsMDS.Rows(rowDataStartMDS).Resize(rowDataEndMDS - rowDataStartMDS + 1) End If
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you I will look them both over.. I truly appreciate all your assistance.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
It looks like a change was not in your 33a, so I put it in 34
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
The State-Region used to be in J:K on Names, now it's in L:N
Capture.JPG
So when looking up a State, no entry was found
That also affected 'Oracle Site Reference'
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
Last edited by pawcoyote; 03-23-2017 at 10:11 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
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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..
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Roger that... I will be starting a new thread soon. This one is complete. I will be using the same workbook but different criteria.