Update Combobox after adding value to dynamic range via another User Form

[zielonapani]

Hello,

I created a user form where I use few Comboboxes (for example: cbFileType or cbTransferType). I have also added buttons that trigger another userforms (for example Add new File Type, Add new transfer Type) that contain textboxes that add new values to corresponding tables.
The problem I have met is as follows:
When I open a main User Form and work through it filling it in...When I come to the combobox (for example cb FileType)..I check the list and realise that the file Type I want to use is not there so I click on the button Add New File Type and a new user form appears where in a text box I type a new File type click confirm button and the the form disappears and I am left only with the main userform. When I click on the combobox File Type again - I expect to have that new type to appear there - but sadly it is not there. When I close the main userform and reopen it again - the combobox is updated BUT I really would like to not to have to close it to see it in the list. I tried to research it through the Internet first but didnt come up with solution. I Paste here my codes. Hopefully someone will have an idea to my problem.Thank you in advance. SJ

USERFORM FOR NEW FILE TYPE named "NewFileType":

Private Sub cmdAdd_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("FileType")




With Application
  .EnableEvents = False
  .ScreenUpdating = False
irow = ws.Cells.Find(What:="*", searchorder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler






            If Trim(Me.txtNewFtype.Value) = "" Then
              Me.txtNewFtype.SetFocus
              MsgBox "Please enter new File Type."
              Exit Sub
            End If
            
ws.Cells(irow, 1).Value = UCase(Me.txtNewFtype.Value)
'clear the data
Me.txtNewFtype.Value = ""
.EnableEvents = True
.ScreenUpdating = True
End With
ActiveWorkbook.Save
Unload Me
' Exit the macro so that the error handler is not executed.
Exit Sub


Errhandler:
 MsgBox "An error has occurred. The macro will end."


End Sub

MAIN USERFORM named "DataOutDeliveryNote"

Private Sub UserForm_Initialize()
Dim txtAQ As Control


Dim cFileType As Range
Dim wsFT As Worksheet
Set wsFT = Worksheets("FileType")


For Each cFileType In wsFT.Range("FileType")
  With Me.cbFileType
    .AddItem cFileType.Value
    .List(.ListCount - 1, 1) = cFileType.Offset(0, 1).Value
  End With
Next cFileType


End Sub

 

[Norie]

One way to update the combobox would be to run this code again after you've added the new file type,

For Each cFileType In wsFT.Range("FileType")
  With Me.cbFileType
    .AddItem cFileType.Value
    .List(.ListCount - 1, 1) = cFileType.Offset(0, 1).Value
  End With
Next cFileType

so I would recommend you put it in a separate sub which can be called from both the Initialize event and after you've added the new file type using the other user form.

Alternatively you could use add the new file type with code in the other userform.

With DataOutDeliveryNote.cbFileType
    .AddItem UCase(Me.txtNewFtype.Value)
End With

 

[zielonapani]

Norie,

oh my...such an easy solution! Thank you so much. Works perfectly!
Have a nice day!
SJ