Hello all, I've got to do a presentation on an excel tool I'm working on, tomorrow. I'm just having this issue with this one macro.
All the macro does is check if various cells are empty, if any of them are, it makes a "Hide" tag, if they are all filled it makes a "Next" button appear. The way I've done the "Checking if cells are empty" is pretty drawn out and primitive if I'm honest, it's a bit all over the place.
I think the issue is with some cells that should only be checked depending on the results of previous cells as I've got other macros that use similar methods and work fine. (This macro also used to work fine until I changed it recently to include those extra bits)
My issue so far is, if I try and run the whole thing in one, it comes up with "Run time error 1004", but if I do it in sections with ctrl+F8 there is no error but it still doesn't have the right output (No next button appears, it acts as if the next button has already been pressed) showing the next section. I'm really confused here and could use some help.
Here is the code, I'm very sorry it's a bit of a mess but if any questions just comment and I'll try and make it clear as possible. (the 'x near every If and End If is to make sure it all matches up)
All the macro does is check if various cells are empty, if any of them are, it makes a "Hide" tag, if they are all filled it makes a "Next" button appear. The way I've done the "Checking if cells are empty" is pretty drawn out and primitive if I'm honest, it's a bit all over the place.
I think the issue is with some cells that should only be checked depending on the results of previous cells as I've got other macros that use similar methods and work fine. (This macro also used to work fine until I changed it recently to include those extra bits)
My issue so far is, if I try and run the whole thing in one, it comes up with "Run time error 1004", but if I do it in sections with ctrl+F8 there is no error but it still doesn't have the right output (No next button appears, it acts as if the next button has already been pressed) showing the next section. I'm really confused here and could use some help.
Here is the code, I'm very sorry it's a bit of a mess but if any questions just comment and I'll try and make it clear as possible. (the 'x near every If and End If is to make sure it all matches up)
Code:
Sub BundleChecks()
ActiveSheet.Unprotect
Application.ScreenUpdating = False
If chkBundle.Value = True Then '1
'2
If Range("E117").Value = "" Or Range("E118").Value = "" Or Range("E121").Value = "" Or Range("E122").Value = "" Or Range("E123").Value = "" Or Range("J122").Value = "" Or _
Range("E124").Value = "" Or Range("J123").Value = "" Or Range("L123").Value = "" Or Range("E125").Value = "" Or Range("E127").Value = "" Or Range("J126").Value = "" Or _
Range("E130").Value = "" Or Range("E131").Value = "" Or Range("J130").Value = "" Or Range("K130").Value = "" Or Range("J131").Value = "" Or Range("E132").Value = "" Or _
Range("E135").Value = "" Or Range("E140").Value = "" Or Range("H140").Value = "" Or Range("J140").Value = "" Or Range("K140").Value = "" Or Range("E142").Value = "" Or _
Range("F142").Value = "" Or Range("H142").Value = "" Or Range("E141").Value = "" Or Range("E145").Value = "" Or Range("E149").Value = "" Or Range("E153").Value = "" Or _
Range("J153").Value = "" Or Range("E155").Value = "" Or Range("J155").Value = "" Or Range("I156").Value = "" Or Range("J156").Value = "" Or Range("E156").Value = "" Or _
Range("E157").Value = "" Or Range("E158").Value = "" Or Range("J158").Value = "" Or Range("E161").Value = "" Or Range("E162").Value = "" Or Range("K161").Value = "" Or _
Range("K162").Value = "" Or Range("K163").Value = "" Or Range("E168").Value = "" Or Range("J168").Value = "" Or Range("M168").Value = "" Or Range("E169").Value = "" Or _
Range("J169").Value = "" Or Range("E173").Value = "" Or Range("E174").Value = "" Or Range("M173").Value = "" Or Range("C179").Value = "" Or Range("D179").Value = "" Or _
Range("E179").Value = "" Or Range("G179").Value = "" Or Range("I179").Value = "" Or Range("K179").Value = "" Or Range("N179").Value = "" Or Range("P179").Value = "" Or _
Range("R179").Value = "" Or Range("E188").Value = "" Then
Range("AK11").Value = "Hide"
Else
Range("AK11").Value = "Show"
End If '2
If Range("E125").Value = "Cartridge" Then '3
If Range("J124").Value = "" Then '4
Range("AK1").Formula = "Hide"
Else
ActiveSheet.Range("AK1").Formula = "Show"
End If '4
Else
ActiveSheet.Range("AK1").Formula = "Show"
End If '3
If Not Range("E135").Value = "Welded" Then '5
If Range("E136").Value = "" Then '6
ActiveSheet.Range("AK2").Formula = "Hide"
Else
ActiveSheet.Range("AK2").Formula = "Show"
End If '6
Else
ActiveSheet.Range("AK2").Formula = "Show"
End If '5
If Range("E135").Value = "Bite Couplings" Then '7
If Range("E137").Value = "" Then '8
ActiveSheet.Range("AK3").Formula = "Hide"
Else
ActiveSheet.Range("AK3").Formula = "Show"
End If '8
Else
ActiveSheet.Range("AK3").Formula = "Show"
End If '7
If Not Range("C180").Value = "" Then '9
If Range("C180").Value = "" Or Range("D180").Value = "" Or Range("E180").Value = "" Or _
Range("G180").Value = "" Or Range("I180").Value = "" Or Range("K180").Value = "" Or _
Range("N180").Value = "" Or Range("P180").Value = "" Or Range("R180").Value = "" Then '10
ActiveSheet.Range("AK4").Formula = "Hide"
Else
ActiveSheet.Range("AK4").Formula = "Show"
End If '10
Else
ActiveSheet.Range("AK4").Formula = "Show"
End If '9
If Not Range("C181").Value = "" Then '11
If Range("C181").Value = "" Or Range("D181").Value = "" Or Range("E181").Value = "" Or _
Range("G181").Value = "" Or Range("I181").Value = "" Or Range("K181").Value = "" Or _
Range("N181").Value = "" Or Range("P181").Value = "" Or Range("R181").Value = "" Then '12
Range("AK5").Formula = "Hide"
Else
ActiveSheet.Range("AK5").Formula = "Show"
End If '12
Else
ActiveSheet.Range("AK5").Formula = "Show"
End If '13
If Not Range("C182").Value = "" Then '13
If Range("C182").Value = "" Or Range("D182").Value = "" Or _
Range("E182").Value = "" Or Range("G182").Value = "" Or Range("I182").Value = "" Or _
Range("K182").Value = "" Or Range("N182").Value = "" Or Range("P182").Value = "" Or _
Range("R182").Value = "" Then '14
ActiveSheet.Range("AK6").Formula = "Hide"
Else
ActiveSheet.Range("AK6").Formula = "Show"
End If '14
Else
ActiveSheet.Range("AK6").Formula = "Show"
End If '13
If Not Range("C183").Value = "" Then '15
If Range("C183").Value = "" Or Range("D183").Value = "" Or _
Range("E183").Value = "" Or Range("G183").Value = "" Or Range("I183").Value = "" Or _
Range("K183").Value = "" Or Range("N183").Value = "" Or Range("P183").Value = "" Or _
Range("R183").Value = "" Then '16
ActiveSheet.Range("AK7").Formula = "Hide"
Else
ActiveSheet.Range("AK7").Formula = "Show"
End If '16
Else
ActiveSheet.Range("AK7").Formula = "Show"
End If '15
If Not Range("C184").Value = "" Then '17
If Range("C184").Value = "" Or Range("D184").Value = "" Or _
Range("E184").Value = "" Or Range("G184").Value = "" Or Range("I184").Value = "" Or _
Range("K184").Value = "" Or Range("N184").Value = "" Or Range("P184").Value = "" Or _
Range("R184").Value = "" Then '18
ActiveSheet.Range("AK8").Formula = "Hide"
Else
ActiveSheet.Range("AK8").Formula = "Show"
End If '18
Else
ActiveSheet.Range("AK8").Formula = "Show"
End If '17
If Range("E188").Value = "Yes" Then '19
If Not Range("F192").Value = "Safe Area" Then '20
If Range("E192").Value = "" Then '21
ActiveSheet.Range("AK9").Formula = "Hide"
Else
ActiveSheet.Range("AK9").Formula = "Show"
End If '21
Else
ActiveSheet.Range("AK9").Formula = "Show"
End If '20
If Range("F192").Value = "" Or Range("J192").Value = "" Then '22
ActiveSheet.Range("AK10").Formula = "Hide"
Else
ActiveSheet.Range("AK10").Formula = "Show"
End If '22
Else
ActiveSheet.Range("AK9").Formula = "Show"
ActiveSheet.Range("AK10").Formula = "Show"
End If '19
If Range("AK1").Value = "Show" And Range("AK2").Value = "Show" And Range("AK3").Value = "Show" And Range("AK4").Value = "Show" And _
Range("AK5").Value = "Show" And Range("AK6").Value = "Show" And Range("AK7").Value = "Show" And Range("AK8").Value = "Show" And _
Range("AK9").Value = "Show" And Range("AK10").Value = "Show" And Range("AK11").Value = "Show" Then '23
If Range("AG4").Text = "Hide" Then '24
Next4.Visible = True
Else
Next4.Visible = False
End If '24
Else
End If '23
Else
ActiveSheet.Range("AG4").Formula = "Show"
End If '1
Application.ScreenUpdating = True
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub
Last edited: