1 of 2 VBA codes not working, need assistance to see why

[benji1973]

I have a VBA code that isn't working, can someone take a look and see if you can let me know. In the attached there are 2 codes, the first one works which allows for multiple selections from a drop down menu. The second one highlighted is the one I am having issues with, it is supposed to make the cells in the code have mandatory data before the user is able to save. Anyone able to help?

[TMS]

Works for me. What doesn't work for you? bear in mind I don't have your workbook, worksheet or data and I have just tested the loop.

Please Login or Register  to view this content.

[benji1973]

it saves without data inputted in those cells, now I am a novice so I may be saving it wrong. does it have anything to do with the first code? are they correct in the way they are written as two separate codes?

[TMS]

As I said, without the workbook, worksheet, data and code, there's not a lot I can do to help you.

The Worksheet Change Event Handler fires when you make a change on the worksheet. The Before Save Event Handler fires when you try to save or close the workbook. There's no interaction or dependency.

As far as I can see, the loop works and will exit if it finds a blank cell. But my test may not reflect your real life data. Who knows? Only you. You could add the Debug.Print into your Before_Close Event Handler and see what it is finding.

[benji1973]

I've attached the worksheet I'm working with and the cells are highlighted, these are the codes in it and as mentioned the first one works second one doesn't.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2019/11/13
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not Application.Intersect(Target, xRng) Is Nothing Then
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" Then
If xValue2 <> "" Then
If xValue1 = xValue2 Or _
InStr(1, xValue1, ", " & xValue2) Or _
InStr(1, xValue1, xValue2 & ",") Then
Target.Value = xValue1
Else
Target.Value = xValue1 & ", " & xValue2
End If
End If
End If
End If
Application.EnableEvents = True
End Sub
_____________________________________________________________________________________
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' variables
mycells = "A20,A131,A152,D12,D13,D14,D15,D16,D17,D20,E17,E20,J21,I13,I15,I17,I21,I145,I147,F16,K1,K2,K5,K6,K7,K8,K20,L20,M1,M14"
mysheet = "sheet1"
' basic code
array1 = Split(mycells, ",")
For i = 0 To UBound(array1)
If Sheets(mysheet).Range(array1(i)) = "" Then
Cancel = True
' your message
MsgBox "Please enter the Required Data"
Exit Sub
End If
Next
End Sub

[TMS]

00:30 am here so not able to test it right now. Sort of feels like Event Handling is being switched off somewhere (and not switched back on). Can't see that happening in the code you've posted though.

I'd us Debug.Print at the start of the Save Code to display a message to see if it actually fires when you save the workbook.

[Fluff13]

You have the code in the wrong place. The BeforeSave code needs to be in the ThisWorkbook module, not the sheet module.

[TMS]

Yep, what Fluff13 said. The Workbook_BeforeSave Event Handler goes in the Workbook Class Module (the clue is in the name )

Please Login or Register  to view this content.

Note that you should a) Dim all your variables and b) indent the code so that you can better see the structure.



Administrative Note:

Regarding Post #5

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.