Sub does not work using button but runs from VisualBasic window just fine

[mysticmario]

I have this macro that populates my materials sheet based on the priced products I use a multifunctional macro for it
This macro should loop through wide variety of ranges and either grab the required data or ask for input to specify additional propeties of the material
here's how it looks

Sub kartarealizacji_Click()
Dim Response As VbMsgBoxResult
Sheets("SZABLON_SZAFA").Unprotect
Sheets("KARTA REALIZACJI").Visible = True
   
    Const strPrompt As String = "Czy projekt jest juz rozpoczęty w systemie?" & vbCrLf & _
                                 "(Aby poprawnie wygenerować Karte Realizacji, projekt musi być OTWARTY w systemie " & _
                                 "(ArtProInfo v1.5/LISTA OTWARTYCH PROJEKTÓW)"
   
    Response = MsgBox(strPrompt, vbYesNo)
        If Response = vbYes Then
LastSh = ActiveSheet.Name

 Sheets("KARTA REALIZACJI").Visible = xlSheetVisible
 Sheets("KARTA REALIZACJI").Copy After:=Sheets(Sheets.Count)
 Sheets("KARTA REALIZACJI").Visible = xlSheetVeryHidden
 ActiveSheet.Name = "Karta Realizacji projektu"
 ProjectSh = ActiveSheet.Name
 Sheets(LastSh).Activate

'EXPORT DANYCH'
    Dim Rng As Range, cell As Range, lr As Long, i&, j&, mtr As Range, paint As Range

    Sheets(ProjectSh).Range("D5") = Date
    lr = 10
    Set paint = ActiveSheet.Range("H43,H44,H76,H77, H78, H109,H110,H111,H142,H143,H175,H176")
    Set mtr = ActiveSheet.Range("E19, E52, E85, E118, E151")
    Set Rng = ActiveSheet.Range("H195:H273")
    For Each cell In mtr
        If Not IsEmpty(cell) And cell.Value <> 0 Then
            lr = lr + 1
           
            Sheets(ProjectSh).Cells(lr, "B").Value = ("Płyta " & cell.Value)
            Sheets(ProjectSh).Cells(lr, "C").Value = cell.Offset(20, 1).Value & "m2"
            lr = lr + 1
            If cell.Offset(20, 4).Value > 0 Then
            Sheets(ProjectSh).Cells(lr, "B").Value = ("Obrzeże " & cell.Value)
            Sheets(ProjectSh).Cells(lr, "C").Value = cell.Offset(20, 4).Value & "mb"
           
            Else:
                lr = lr - 1
                GoTo nextcell
               
            End If
        End If
nextcell:             Next cell
    lr = lr + 1
    For Each cell In paint
            If Not IsEmpty(cell) And cell.Value <> 0 Then
            Dim lakier As String
            lakier = InputBox("Dla pozycji: " & "" & cell.Offset(, -4).Value & " - " & cell.Value & "m2" & " " & "jakiego lakieru?", "RODZAJ, TYP, DOSTAWCA (PRZYKŁAD: BEZBARWNY HD CRYL - MARIANUS)")
            Sheets(ProjectSh).Cells(lr, "B").Value = ("Lakier: " & lakier)
            Sheets(ProjectSh).Cells(lr, "C").Value = cell.Value & "m2"
            lr = lr + 1
            Else:
                GoTo nextitem
            End If
               
nextitem:    Next cell
    lr = lr + 1
    Call export_acc
Else

    MsgBox "Wprowadź projekt do systemu": Exit Sub
    End If
Sheets("SZABLON_SZAFA").Protect
    End Sub
    Private Sub export_acc()
    Sheets("SZABLON_SZAFA").Unprotect
    Dim Rng As Range, cell As Range, lr As Long, i&, j&, S, T%, X
    Dim ws As Worksheet
    Application.ScreenUpdating = 0
    Set ws = Sheets(ProjectSh)
    Set Rng = Sheets(LastSh).Range("H195:H273")
    S = Array("B", "K", "T")
    With ws
        T = Application.WorksheetFunction.CountA(.Range("B11:B30,K11:K30,T11:T30"))
        If T >= 60 Then MsgBox "Full line.Please check data": Exit Sub
        If T > 0 Then
            X = Int(T / 20)
            lr = .Cells(30, S(X)).End(3).Row
        Else
            lr = 10
            X = 0
        End If
    End With
    For Each cell In Rng
        If Not IsEmpty(cell) And cell.Value <> 0 Then
            lr = lr + 1
            If lr < 31 Then
                If X <= 2 Then
                    ws.Cells(lr, S(X)).Value = Sheets(LastSh).Range("D" & cell.Row).Value & " " & Sheets(LastSh).Range("E" & cell.Row).Value
                    ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Value & cell.Offset(0, 1).Value
                Else
                    MsgBox "Check the return area": Exit Sub
                End If
            Else
                X = X + 1
                lr = lr - 20
                If X <= 2 Then
                    ws.Cells(lr, S(X)).Value = Sheets(LastSh).Range("D" & cell.Row).Value & " " & Sheets(LastSh).Range("E" & cell.Row).Value
                    ws.Cells(lr, S(X)).Offset(, 1).Value = cell.Value & cell.Offset(0, 1).Value
                Else
                    MsgBox "Check the return area": Exit Sub
                End If
            End If
        End If
    Next cell
    Application.ScreenUpdating = 1
    MsgBox "Done"
        Sheets("Karta Realizacji projektu").Activate
    'ActiveSheet.Range("C3:H3").Select
    'With Selection.Validation
        '.Delete
        '.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, operator:= _
        'xlBetween, Formula1:="='OTWARTE PROJEKTY'!$B$3:$B$70"
        '.IgnoreBlank = True
        '.InCellDropdown = True
        '.InputTitle = ""
        '.ErrorTitle = ""
        '.InputMessage = ""
        '.ErrorMessage = ""
        '.ShowInput = True
        '.ShowError = True
    'End With
    Sheets("SZABLON_SZAFA").Protect
End Sub

I have it assigned to a button, but the problem is that if I press the button macro prompts

Const strPrompt As String = "Czy projekt jest juz rozpoczęty w systemie?" & vbCrLf & _
                                 "(Aby poprawnie wygenerować Karte Realizacji, projekt musi być OTWARTY w systemie " & _
                                 "(ArtProInfo v1.5/LISTA OTWARTYCH PROJEKTÓW)"

and if I press "yes" i does not execute the code that should be executed fi the response is yes it skips entire code up to this point MsgBox "Done"
If i press "NO" it executes the the "Else" condition:
Else MsgBox "Wprowadź projekt do systemu": Exit Sub

But if I click "assign macro..." and "edit" on the button itself and run the code frow VisualBasic window (F5 or play button) it works perfectly fine
I tried to copy and paste the code to new sub and then apply it to the button but that didnt help aswell.

Can anyone help me out with this one?

 

[mysticmario]

Given that you are activating sheets in your code, you should probably be disabling events at the start of the code and then re-enabling them at the end.

I commented them out entirely and it didnt help.
I moved them to the start and end of code and still the macro doesn't work by pressing the button.
and it still works perfectly fine from VBA window.

 

[RoryA]

Have you stepped through the code manually? I don't see how it can just skip entire lines that are not conditional on something.

At this point it might help if you could post a workbook somewhere (e.g. Onedrive or dropbox) with no data so that we can have a look and see if the same behaviour persists.

 

[mysticmario]

Have you stepped through the code manually? I don't see how it can just skip entire lines that are not conditional on something.

At this point it might help if you could post a workbook somewhere (e.g. Onedrive or dropbox) with no data so that we can have a look and see if the same behaviour persists.

The actual code works perfectly fine.
@Flashbond actually had this workbook opened in his excel app, if the profile information is right it was excel 2019 and the button worked perfectly fine, for some reason on my 365 it doesnt work.
and this is the only button that does not work. I can send you the file if you provide me your email adresse for example.

 

[RoryA]

Do you not have access to Onedrive or similar?

 

[Legacy 143009]

The actual code works perfectly fine.
@Flashbond actually had this workbook opened in his excel app, if the profile information is right it was excel 2019 and the button worked perfectly fine, for some reason on my 365 it doesnt work.
and this is the only button that does not work. I can send you the file if you provide me your email adresse for example.

Yes, it is Professional Plus 2019.

 

[mysticmario]

ARTPRO-Wyceny.rar

drive.google.com

INSTRUCTIONS:
for login use: Mariusz PW: admin
after login browse for a save folder next to H17 (currently set for my onedrive), and press "Zapisz" - to save it
there is a sheet that i made for you named "M_TEST"
on that sheet you have 2 buttons,
first one to the left will save your sheet into a new file in your given location, in this newly created file 3rd button will appear in "M_TEST, when you press it you will basically make another copy of "M_TEST", but this time with a different name. on that sheet the 4th button will appear, and this is the one that doesn't run the macro, but when u right click the button >>assign macro>>edit>>f5 the macro will run perfectly fine.

 

[mysticmario]

I did a crazy workaround...
Since userForms work perfectly fine with co-authoring, I made the button to open a userform to confim the material sheet generation, and voila code run from useform button works perfectly fine. You know what they say if something looks stupid but works it's not stupid.
Unless omeone figures out different solution I'll mark this one. I migth be the only case where this bug exist, but maybe one day someone will need it.
Thank you for your assistance.

 

[Legacy 143009]

You are slowly getting the poison This is how I started to programming 15 years ago...
First you are going to say "Lets design some user inputs". Then "why not to design all project with user forms?"
"So now I learned how to write Visual Basic. Can I write this program in Visual Studio?"
"I can call the data from Excel files"
"Opening and closing excel files makes no sense. Why am I not moving my data to access database?
"Why am I not moving all my data to an actual database like SQL?"
And voila! You have a fully functional desktop application

 

[mysticmario]

I was planning to start with pyhton, but man I have to little kids at home I'm 32yrs old, and not much time on my hands. To start with python i would need a guide of some sort. cause some things does not click with me when it comes to full scale programming. I like VBA due to it's being sort of like html. You have this already created GUI that u can drag and drop modify in form of excel app, therefore you can immediately see the effects of your work. And focus more on the lolgic side of programming. This is not the case with Pyhton. people say that the actual python coding is better because you can achieve the same effect with less code but I dont really belive that, simply because if you wnat to have visual respresentation of your script, you need to program a button and a window and textbox with code, defining all of its parameters. I wish I could program well, despite having so many problems with my VBA projects i love it and time flies by if I code at work - which is actually not my job I just like to do it.

 

[mysticmario]

I'm still far off from being good with VBA I started using VBA more "hardcore" in November 10 2021 - the day I joined this forum.It's been a year so far and I am proud of myself that I was able to transfer my company from pen and paper to full digital. With windows tablets hanging around the warehouse showing project numbers imported to html from my main xlsm file, a in'n'out clocking devices also made in VBA which read the barcodes from cards. CSV data source based warehouse supply system. Quotation making sheet with ability to generate material sheet for production. So much stuff, that was really fun to make. My company due to this forum and many hours put in by me can finally make a solid report of finished projects which sums up all the hours and costs gathered from various sources and generate full summary with conclusions and stuff - we can finally see clearly fi we're profitable or not.
Unfortunately many of the functions I still wantg to have, died so far cause I cannot get through them but I'm looking forward to getting back to them.
Unfortunately there are also downsides... Like my 8h day takes now about 2 hours to finish all the work due to all the macros I have... So sometimes I struggle to find something to do. But I like these downside more than any other - it helps me to get back to coding my excel sooner