Results 1 to 3 of 3

Thread: Advice for existing working code on userform

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Advice for existing working code on userform

    Hi,
    I believe the code supplied below is what you require.
    I have a worksheet database where i send out parcels to customers & once parcel is received i like to make a note of the date on my database.
    I have a drop down list on my userform which looks at the worksheet & loads any customers name which doesnt have a delivered date assisgned to them.

    Everythings works fine but i need to change / alter one thing that happens.

    I click on the drop down & select the customers name.
    I then press the transfer button which then enters the date next to that customer in question.
    I see a msgbox telling me worksheet updated sucessfully & i then click on ok.
    At this point if i look in the drop down list this customers name is tsill there.
    It is only gone once i close the userform.

    I would like the name to be removed from the drop down list when i see the sucessfull message.





    Code:
    Private Sub DateTransferButton_Click()'Dantes code
        Dim sh As Worksheet
        Dim b As Range
        Dim wName As String, res As Variant
        
        If NameForDateEntryBox.ListIndex = -1 Then
            MsgBox "Please Select A Customer Before Pressing Transfer Button", vbCritical, "Delivery Parcel Date Transfer Message"
            Exit Sub
        End If
        
        If TextBox7.Value = "" Or Not IsDate(TextBox7.Value) Then
            MsgBox "Please Enter A Valid Date", vbCritical, "Delivery Parcel Date Transfer Message"
            TextBox7 = ""
            TextBox7.SetFocus
            Exit Sub
        End If
        
        wName = NameForDateEntryBox.List(NameForDateEntryBox.ListIndex)
        Set sh = Sheets("POSTAGE")
        Set b = sh.Columns("B").Find(wName, LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then
            If sh.Cells(b.Row, "G").Value <> "" Then
                MsgBox "DATE HAS BEEN ENTERED ALREADY !" & vbCrLf & "Click OK To Go Check It Out ", vbCritical, "Delivery Parcel Date Transfer Message"
                TextBox7 = ""
                Unload PostageTransferSheet
                Cells(b.Row, "G").Select
            Else
                sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value)
                MsgBox "Delivery Date Updated Sucessfully", vbInformation, "Delivery Parcel Date Transfer Message"
                End If
        End If
        NameForDateEntryBox = ""
        TextBox7 = ""
        TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
    End Sub
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    798
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advice for existing working code on userform

    Closing and re-opening the form is re-running the form initialization sub.
    You could do the same without closing the form by inserting Call UserForm_Initialize after the message box.

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Advice for existing working code on userform

    Thanks very much
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •