exporting userform - doesn't work for other PC than mine

mysticmario

Active Member
Joined
Nov 10, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
Hi,
I'm coming back to you with userform exporting issue once again.
Below macro creates new workbook with userform from previous workbook amongst other things
so it export temporary userform files and then imports them into new workbook.
Entire code works works great on my PC.
Unfortunately on other PCs i get error at line srcWB.VBProject.VBComponents("kartaR").Export _

Run-time error 1004
Application-defined or object-defined error

It is sort of like it does not find those useforms there

VBA Code:
Sub ZapiszWchmurze_Click()
ActiveSheet.Unprotect
    Dim wbname As String, wbaddress As String
    Dim wbOrig As Workbook, wbnew As Workbook
    Dim shtNames As Variant, sName As Variant
    Dim wbname2 As String
    Dim sheetName As String
    Dim usercheck As String
    Dim pathStorage As String
    Dim lr As Long
    sheetName = ActiveSheet.Name
    lr = 3
    
    Application.ScreenUpdating = False
    Set wbOrig = ThisWorkbook
    wbname = ActiveSheet.Name
    usercheck = InputBox("Wpisz ponownie hasło", "PODAJ HASŁO")
    If usercheck = "witek" Then
    wbaddress = wbOrig.Worksheets("Admin").Range("A25").Value
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "B").Value = sheetName
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "C").Value = Sheets(sheetName).Range("D16").Value
    ElseIf usercheck = "konrad" Then
    wbaddress = wbOrig.Worksheets("Admin").Range("A26").Value
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "G").Value = sheetName
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "H").Value = Sheets(sheetName).Range("D16").Value
    ElseIf usercheck = "damian" Then
    wbaddress = wbOrig.Worksheets("Admin").Range("A27").Value
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "L").Value = sheetName
    Sheets("ZESTAWIENIE WYCEN").Cells(lr + 1, "M").Value = Sheets(sheetName).Range("D16").Value
    ElseIf usercheck = "admin" Then
    wbaddress = wbOrig.Worksheets("Admin").Range("A28").Value
    Else
    MsgBox "Nieprawidłowe hasło"
    Exit Sub
    MsgBox wbaddress
    
    End If
    wbname2 = InputBox("Podaj nazwę", "Nazwa pliku")
    'wbaddress = wbOrig.Worksheets("PANEL WYCEN").Range("H17").Value
    shtNames = Array("MAG", "SZABLON_SZAFA", "KARTA REALIZACJI", "OFERTA", "Admin", wbname)
    Dim srcWB As Workbook
    Dim destWb As Workbook
    Dim sStr As String
    sStr = wbaddress & "\" & tempFile & ".frm"
    Set wbnew = Workbooks.Add(xlWBATWorksheet)  'Create new wb with a single blank sheet
    wbOrig.Activate
    Set srcWB = ThisWorkbook
    Set destWb = wbnew
    'get kartaR'
    srcWB.VBProject.VBComponents("kartaR").Export _
    Filename:=sStr
    destWb.VBProject.VBComponents.Import _
    Filename:=sStr
    Kill sStr
    'get mag'
    srcWB.VBProject.VBComponents("wyszukiwarka").Export _
    Filename:=sStr
    destWb.VBProject.VBComponents.Import _
    Filename:=sStr
    Kill sStr
    For Each sName In shtNames
        wbOrig.Worksheets(sName).Copy After:=wbnew.Worksheets(wbnew.Sheets.Count)
    Next sName
   
    Application.DisplayAlerts = False
        wbnew.Worksheets(1).Delete
        'wbOrig.Worksheets(wbname).Delete
       
        wbnew.SaveAs Filename:=wbaddress & "\" & wbname2 & ".xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        'wbNew.Close savechanges:=False             '<--- Uncomment this if you want to close the new workbook
        wbnew.Worksheets("MAG").Visible = xlSheetVeryHidden
        wbnew.Worksheets("KARTA REALIZACJI").Visible = xlSheetVeryHidden
        wbnew.Worksheets("SZABLON_SZAFA").Visible = xlSheetVeryHidden
        wbnew.Worksheets("Admin").Visible = xlSheetVeryHidden
        wbnew.Worksheets(sheetName).Buttons.Delete
Dim i As Integer
Dim sheetNumber As String



sheetName = ThisWorkbook.ActiveSheet.Name
sheetNumber = ThisWorkbook.ActiveSheet.CodeName & "."
i = 0
i = i + 1
Call Create_Button(sheetName, "D1:E1", "Zapisz i zakończ", sheetNumber + "ZapiszWchmurze_Click", i)
i = i + 1
Call Create_Button(sheetName, "G1:H1", "Sprawdź magazyn", sheetNumber + "magazyn_Click", i)
i = i + 1
Call Create_Button(sheetName, "J1:L1", "Utwórz plik precyzyjnej wyceny", sheetNumber + "fullprice_Click", i)
i = i + 1
Call Create_Button(sheetName, "M1:N1", "Utwórz kartę realizacji", sheetNumber + "kartarealizacji_Click", i)
i = i + 1
Call Create_Button(sheetName, "R2:S2", "POLSKA", sheetNumber + "polska_Click", i)
i = i + 1
Call Create_Button(sheetName, "U2:V2", "WIELKA BRYTANIA", sheetNumber + "anglia_Click", i)


        
        'wbNew.Close savechanges:=False             '<--- Uncomment this if you want to close the new workbook
       
    Application.DisplayAlerts = False
wbnew.ActiveSheet.Buttons("button_3").Visible = True
wbnew.ActiveSheet.Buttons("button_4").Visible = False
wbnew.ActiveSheet.Buttons("button_1").Visible = False
ActiveSheet.Protect
   
End Sub

I'm loosing my mind over this cause i worked on this script for days, upgraded it with newer and newer functions but i always tested this on my PC, now when I need it to run for soemone else it breaks.... help please
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Do you have the Trust Centre on those machines set to allow access to the VBA project? The code won't work without that.
 
Upvote 0
Solution
Anyone using that code would need to explicitly lower their security settings for the code to work. You may have done this on your machine and not instructed others to do so, depending on policy they may not be able to actually do it on their version of office. The setting you're looking for is "Enable trust Access to the VBA project object Model", but there are very good reasons for not having this ticked by default.
 
Upvote 0
I know that this code requires that I am the permission guy in the company so I will be able to setup this, I completely forgot I've done this on my machine I know it involves the risk of running harmful code without users knowledge. We will see if this helps, I'll test it when I'm back at the office
 
Upvote 0
It works, perfectly and I feel dumb cause I had the same issue on my own machine and I needed to enable this option aswell...
 
Upvote 0
There are probably better ways of doing this that don’t require the permissions changing. An excel template may be what you’re after
 
Upvote 0
I'll give you a brief desciption of the functionality and reasoning behidn it.

I have a main file where all the deisgners log in and create their preliminary quotations, after they finish it, I need to take their quotation to new file with client name on it, and after that, when client accepts the offer I need to create new detailed quotations with all the actual material details provided by the client. after that we need to be able to generate material sheet for pre production. Each seperate project needs to be it's own database for the project.
and at first I didn't need to use user form cause I had everythign coded in the main file. Unfortunately due to unknown reasons the code that auto generated material sheet from detailed quotations did not work when executed from a button and i spent days on this forum tryin to fix and no1 was able to help with this matter. The problem I encountered was that code run great when started from alt+F11, the same code didnt work when it was assigned to the button. I couldn't use activeX buttons cause of co-authoringg. So the only solution was to make a new userform with this code which worked, and then matter for export impurt user form came about.
 
Upvote 0
many many other things didnt go right wiht my applications, I even had an issue where I had to switch all my buttons to form controls from activex, therefore code behind those button was written on the sheet and not the button like it was the case with activex buttons. due to all the sheet copying, sheet renaming, and listings I couldn't use modules aswell which in the end resulted in me having to create macro to create new buttons on new sheet that would reference macros on this sheet, cause when u try to copy one sheet for e.g. a template of some sorts with buttons on it as new sheet those buttons keep the reference to macro from "old" sheet. VBA is freaking crazy and now i generally know why you dont really use it to create app-like products.
 
Upvote 0
I suspect a better approach would have been to create an excel template (xltm) file with your modules, form and everything set up as a blank file without the quotation numbers in. When they're ready to create the customer specific version from the version they have to work with, create a new workbook from the xltm (which will include all the userforms, modules code etc) and copy the values into it that the designers have entered.

I do something similar for a client who has timesheets where the users enter hours, pay rates etc. But he has dozens of different clients, each client has their own specific information and settings stored within their timesheet workbook. However, there is only one master excel workbook template (that containes all the sheets, code, forms etc) so that updates are straightforward - when code changes are needed, we loop through the existing client workbooks and create a new workbook from the template for each one and copy their specific information into it.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,335
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top