CODEWITHSUNDEEP
excelvba
Freek v. wely
Freek v. wely

Reputation: 111

The result of "=Range ("H28")=Range("H28") & Format(Date, " dd-mm-yyyy")" in textbox in userform

I have make a userform with a textbox in it. I want to fill the textbox with the result of "=Range("H28") & Format(Date, " dd-mm-yyyy")". Is this possible? Is it also possible to use the amended text in the textbox in the userform as filename? Now I'am using the next code for save as:

'   Set File Filter
    Filt = "Excel Files with macro (*.xlsm), *.xlsm"
    '   Set *.* to Default
'   Set Dialogue Box Caption
    Title = "Please select a different File"
'   Get FileName
    FileName = Application.GetSaveAsFileName(InitialFileName:=Range("H28") & Format(Date, " dd-mm-yyyy"), FileFilter:=Filt, _
        FilterIndex:=FilterIndex, Title:=Title)

Is it possible to use the text of textbox as filename instead of :=Range("H28") & Format(Date, " dd-mm-yyyy")

Upvotes: 0

Views: 112

Answers (3)

Freek v. wely
Freek v. wely

Reputation: 111

I have solved the problem.

For automatic filling textbox4:

Dim h As String
Dim dt As String
Dim hdr As String

h = Range("H28").Value
dt = Format(Date, "dd-mm-yy")
hdr = h & "_" & dt
TextBox4 = hdr

For using the text of Textbox4 for filename the next solution:

Dim Mydir As String
Dim Myfile As String
Dim stGebTekst As String

stGebTekst = UserForm1.TextBox4
Mydir = Range("O1").Value
Myfile = Mydir & "\" & stGebTekst & ".xlsm"

Upvotes: 0

Freek v. wely
Freek v. wely

Reputation: 111

Private Sub UserForm_Initialize()

            Dim h As String
            Dim dt As String
            Dim hdr As String

            h = Range("H28").Value
            dt = Format(Date, "dd-mm-yy")
            hdr = h & "_" & dt
            TextBox4 = hdr

    End Sub

I have with the above code fill textbox4 and it works. Now I want use these possible custom content use in an another code.

    Dim Mydir As String
Dim Myfile As String
Dim TxtBox4 As String

Mydir = "C:\Users\Beheerder\Documents\Test"
Myfile = Mydir & "\" & TxtBox4 & ".xlsm"

'   Controleer of een bestandsnaam al bestaat.
If Dir(Myfile) <> "" Then
    MsgBox "Het bestand bestaat al, benoem het anders!", vbOKOnly
 Else
    MsgBox "Het bestand bestaat nog niet, opslaan?", vbOKOnly
'   Save Workbook
    With ActiveWorkbook
    .Saveas FileName:=(Myfile), FileFormat:=52, CreateBackup:=False
    '.Close False
    End With
'   Display Full Path & File Name
    Response = MsgBox("Gelukt " & Myfile, vbInformation, "Proceed")
End If

The code stops on ".Saveas FileName:=(Myfile), FileFormat:=52, CreateBackup:=False". When I hover over I see that Txtbox4 gives TxtBox4 = ""

Is someone who can help me

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6982

Sure, I tested this on a small userform example

    Private Sub CommandButton1_Click()
    Dim Mydir As String
    Dim Myfile As String

    Mydir = "C:\Users\Dave\Downloads\May 15\"
    Myfile = Mydir & TextBox1 & ".xlsm"


    With ActiveWorkbook
        .SaveAs (Myfile), FileFormat:=52, CreateBackup:=False
        '.Close False
    End With

End Sub


Private Sub UserForm_Initialize()
    Dim h As String
    Dim dt As String
    Dim hdr As String

    h = Range("H8").Value
    dt = Format(Date, "dd-mm-yy")
    hdr = h & "-" & dt
    TextBox1 = hdr

End Sub

Upvotes: 0

Related Questions