Need some advice for code edit please

[ipbr21054]

Hi,

Please see the code in use.
On my worksheet i select a name from textbox called NameForDateEntryBox
I then press the command button called DateTransferButton


What this does is put todays date against the customer selected on the worksheet.
This all works fine BUT when i first started this something wasnt working correct.
The part of the code in question is after i press the command button the userform closes then opens again.

From memory if i didnt had it close/open i couldnt get it to work.

Can you see a way to allow the date to be just entered without the need for the userform to close / open every time.

Private Sub DateTransferButton_Click()
    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 Transfer Button", vbCritical, "Delivery Parcel Date Transfer"
        Exit Sub
    End If
    
    If TextBox7.Value = "" Or Not IsDate(TextBox7.Value) Then
        MsgBox "Please Enter A Valid Date", vbCritical, "Delivery Parcel Date Transfer"
        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") <> "" And UCase(sh.Cells(b.Row, "G")) <> "POSTED" Then
            MsgBox "DATE HAS BEEN ENTERED ALREADY !" & vbCrLf & "CLICK OK TO GO CHECK IT OUT", vbCritical, "Delivery Parcel Date Transfer"
            TextBox7 = ""
            Unload PostageTransferSheet
            Cells(b.Row, "G").Select
        Else
            sh.Cells(b.Row, "G").Value = CDate(TextBox7.Value)
            sh.Cells(b.Row, "G").Interior.Color = vbYellow
            MsgBox "DELIVERY DATE NOW APPLIED TO WORKSHEET", vbInformation, "DELIVERY PARCEL DATE TRANSFER MESSAGE"
            Unload PostageTransferSheet
            PostageTransferSheet.Show
            
        End If
    End If
    NameForDateEntryBox = ""
    TextBox7 = ""
    TextBox7.Value = Format(CDbl(Date), "dd/mm/yyyy")
End Sub

 

[NoSparks]

can't see the 2 red lines as doing anything other than re-initialize the form by closing and re-opening,
try replacing with UserForm_Initialize

 

[ipbr21054]

Hi,
I’m out at present but from memory if I select a name then pressed the command button the name is still shown in the drop down list.
only way to remove it after pressing the command button was to close then open userform. Then when I looked in the drop down it was fine.

so do I just remove those two lines of code and replace it with what you advised.

 

[Sunjinsak]

I’m out at present but from memory if I select a name then pressed the command button the name is still shown in the drop down list.
only way to remove it after pressing the command button was to close then open userform. Then when I looked in the drop down it was fine.

So should the drop-down list's value default to "" (blank) after the button has been clicked? Or do you need the name removing from the drop-down list altogether?

Is the drop-down list on the UserForm? If so what type of control is it? Presumably a ComboBox?

 

[ipbr21054]

Let’s say these names are in the list.
TOM JONES.
CILLA BLACK.
FRED LYON.

I select TOM JONES and press command button.
now it adds the date to the worksheet.
if the userform doesn’t close then open the list stays the same.
I need TOM JONES removed when I press command bottom.

 

[Sunjinsak]

Seen as you mentioned a drop-down list I'm going to assume you're using a ComboBox control. If not post back with what you're using for your drop-down list and I can amend the code for you if necessary.

Removing an item from a ComboBox is as simple as:

    ComboBox1.RemoveItem "TOM JONES"

Change ComboBox1 to the actual name of your ComboBox.


You could store the current value of the ComboBox in a variable and then remove it like so each time the button is clicked...

    Dim nameInList As String
    nameInList = ComboBox1.Value

    'The rest of your code here

    ComboBox1.RemoveItem nameInList

How are you populating the drop-down list in the first place? Depending on where you're populating the list from then simply removing items at run-time won't prevent the list being re-populated with those same items next time you load the UserForm, or is that not an issue in your use-case?

 

[ipbr21054]

I dont understand how your code is going to help.
The names are random depending on purchases etc.

I cant apply TOM JONES to the code as i would also have to do that to all the others and i dont think this is what i need.

I will try nosparks first

 

[Sunjinsak]

Did you see the second example I posted? That should help do what you want. The first was just an example of how to use the .RemoveItem method of a ComboBox.

Presumably the list will already be populated with the name when you click the button, right? If so that second bit of code I posted would assign the name to a variable (so it will contain whichever name is displayed in the box when the button is clicked), you can then do all the other stuff you want to do (e.g. the code you initially posted, minus the closing and opening of the user form) then finally you call the .RemoveItem method of the ComboBox and feed it the variable (which contains the name that is currently displayed).

I hope that makes sense?

Here's the code again...

    Dim nameInList As String
    nameInList = ComboBox1.Value

    'The rest of your code here

    ComboBox1.RemoveItem nameInList

Unless I'm misunderstanding what it is you're trying to achieve? Sorry if that's the case and I'm confusing matters.

 

[ipbr21054]

Thanks very much.
I used @NoSparks code which was UserForm_Initialize and that did it fine.

thanks for all your time.