saving vba variable

[B-Man]

I have a vba macro open a workbook and pull some data to display in a user form and it works fine.

Now I want to close the workbook it opens before it displays the data but when I close the workbook after getting the values but before displaying it they don't display. I don't know if I need to dim as variant and set the values but when I try that I get an object error. so im now out of my depth.

ie. my vba contains a heap of these from textbox 1 to 10, but when I close the workbook the user form just has blank values. (note bImported is just stolen from another piece of code I had that works how I want and just haven't renamed it to suit...)

Giftcards.TextBox1 = Format(wb.Sheets("Giftcards").Range("T2").Value, "$   #,###")

    ' Close the workbook if it was opened by the code
            If bImported Then
            wb.Close SaveChanges:=False
            End If
    
Application.ScreenUpdating = True
Giftcards.Show

 

[6StringJazzer]

Do you have any code in the module for Giftcards?

 

[B-Man]

Do you have any code in the module for Giftcards?

Sub OpenGiftcards()

   Dim wb As Workbook
    Dim wbName As String
   
Application.ScreenUpdating = False

    ' Set the workbook name and path
    wbName = "giftcard.xlsm"


    ' Check if the workbook is already open
    On Error Resume Next
    Set wb = Workbooks(wbName)
    On Error GoTo 0


    ' If the workbook is not open, open it
    If wb Is Nothing Then

    ThisWorkbook.Activate

    ThisWorkbook.Sheets("Sheet1").Range("Giftcards").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
   
    Set wb = ActiveWorkbook
    wb.Activate
   
    bImported = True
   
    End If

    ' Your code to manipulate the workbook goes here
Giftcards.TextBox1 = Format(wb.Sheets("Giftcards").Range("T2").Value, "$   #,###")
Giftcards.TextBox2 = Format(wb.Sheets("Giftcards").Range("T3").Value, "$   #,###")
Giftcards.TextBox3 = Format(wb.Sheets("Giftcards").Range("T4").Value, "$   #,###")
Giftcards.TextBox4 = Format(wb.Sheets("Giftcards").Range("T5").Value, "$   #,###")
Giftcards.TextBox5 = Format(wb.Sheets("Giftcards").Range("T6").Value, "$   #,###")
Giftcards.TextBox6 = Format(wb.Sheets("Giftcards").Range("T7").Value, "$   #,###")
Giftcards.TextBox7 = Format(wb.Sheets("Giftcards").Range("T8").Value, "$   #,###")
Giftcards.TextBox8 = Format(wb.Sheets("Giftcards").Range("T9").Value, "$   #,###")
Giftcards.TextBox9 = Format(wb.Sheets("Giftcards").Range("T10").Value, "$   #,###")
Giftcards.TextBox10 = Format(wb.Sheets("Giftcards").Range("T11").Value, "$   #,###")
Giftcards.TextBox11 = Format(wb.Sheets("Giftcards").Range("T12").Value, "$   #,###")
Giftcards.TextBox12 = Format(wb.Sheets("Giftcards").Range("T13").Value, "$   #,###")
Giftcards.TextBox13 = Format(wb.Sheets("Giftcards").Range("T14").Value, "$   #,###")



    ' Close the workbook if it was opened by the code
            If bImported Then
            wb.Close SaveChanges:=False
            End If
   
Application.ScreenUpdating = True
Giftcards.Show



End Sub

this is the code run by my personal.xlsb module
no code for the giftcard userform

i was hoping it would be something simple to remember the values

like
dim gctb1 as variant or string

gctb1 = Format(wb.Sheets("Giftcards").Range("T2").Value, "$ #,###")

but basically as soon as I close the workbook the values that are set become "object variable or with block variable not set" ie "Giftcards.TextBox1"

Its probably something really easy but I literally learn my code from googling and piecing it together so never started at the basics. and without wording what I want correctly I don't know how to find the answer

 

[6StringJazzer]

It seems to me that loading the values into textboxes, and then closing the source file after that should not cause this error. The error would only occur if you try to run this code and the file isn't open.

the values that are set become "object variable or with block variable not set" ie "Giftcards.TextBox1"

Are you saying that the textboxes now contain this text? That can't be what is happening. The text you quoted is a runtime error that would be shown in a pop-up dialog box when a line of code fails to execute. What line of code is highlighted when this error occurs?

 

[B-Man]

It seems to me that loading the values into textboxes, and then closing the source file after that should not cause this error. The error would only occur if you try to run this code and the file isn't open.

Are you saying that the textboxes now contain this text? That can't be what is happening. The text you quoted is a runtime error that would be shown in a pop-up dialog box when a line of code fails to execute. What line of code is highlighted when this error occurs?

So I don't actually get that error as such when running the macro the text boxes are just blank.
When stepping through the code you can see the textbox code getting populated with the values (the hover over tool tip popup) but as soon as the workbook closes the values that were previously displayed in the hover over popup then change to that error.

If I comment out closing the workbook it all works as it should...

 

[6StringJazzer]

This is a very difficult diagnosis without being able to run the code in your file.

I'm still not understanding what behavior you are seeing. Specifically I don't know what you mean by "hover over tool tip popup". Do you mean if the UserForm is displayed, and you hover over the text box, you see this error message? As I said, that is a runtime error and your code should be interrupted at the point it occurs.

When is sub OpenGiftcards called, and from where?

 

[B-Man]

This is a very difficult diagnosis without being able to run the code in your file.

That is my code. The only thing you don't see is my userform
Or the values it pulls.
Works fine without closing the workbook. just means I have to manually close it?

I'm still not understanding what behavior you are seeing. Specifically I don't know what you mean by "hover over tool tip popup". Do you mean if the UserForm is displayed, and you hover over the text box, you see this error message? As I said, that is a runtime error and your code should be interrupted at the point it occurs.

In the vba macro debug menu when pressing F8 to step through the code line by line.
If I hover the mouse over the word
"Giftcards.TextBox1" in the vba code I get the popup with the value. (Almost like making a comment on a cell and when hovering over the cell it displays the comment)

When is sub OpenGiftcards called, and from where?

Opening it via a macro shortcut key from my personal.xlsb file

Thanks for your help but I think we are just going in circles.
Might have to try a different code or way around it.

 

[dmt32]

I have a vba macro open a workbook and pull some data to display in a user form and it works fine.

Now I want to close the workbook it opens before it displays the data but when I close the workbook after getting the values but before displaying it they don't display.

Bit of a guess & not tested but see if this update to your code will resolve your issue

Sub OpenGiftcards()
    
    Dim wbGiftCard      As Workbook
    Dim wsGiftCards     As Worksheet
    Dim wbName          As String
    Dim arr             As Variant
    Dim i               As Long
    Dim bImported       As Boolean
    
    'specify the workbook path
    Const wbPath   As String = "C:\Users\B-Man\Documents\MyFolder\"
    
    'Set the workbook name
    wbName = "giftcard.xlsm"
    
    On Error Resume Next
    ' Check if the workbook is already open
    Set wbGiftCard = Workbooks(wbName)
    
    'if not, then open it
    If wbGiftCard Is Nothing Then
        Application.ScreenUpdating = False
        Set wbGiftCard = Workbooks.Open(wbPath & wbName)
        bImported = True
    End If
    
    On Error GoTo myerror
    
    'if workbook not found, raise error
    If wbGiftCard Is Nothing Then Err.Raise 53
    
    Set wsGiftCards = wbGiftCard.Worksheets("Giftcards")
    
    'get values from range
    arr = wsGiftCards.Cells(2, 20).Resize(13).Value
    
    ' Close the workbook if it was opened by the code
    If bImported Then wbGiftCard.Close SaveChanges:=False
    
    'load textboxes from array
    For i = 1 To 13
        GiftCards.Controls("TextBox" & i).Value = Format(arr(i, 1), "$   #,###")
    Next i
    
    'display userform
    Application.ScreenUpdating = True
    GiftCards.Show
    
myerror:
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    
End Sub

You will need to adjust the workbook path shown in BOLD as required

Dave

 

[6StringJazzer]

That is my code. The only thing you don't see is my userform
Or the values it pulls.

Right. That's what I would normally need. I have your code but it is useless to me without the UserForm, or the other file you are getting data from. Since I am unable to figure out what's wrong from analyzing the code statically I'll step aside and hopefully someone else will see an issue.

 

[B-Man]

im flat out at the moment so will try that when I can get back to the PC cheers