Updating Cell Values with UserForm

[jadox]

Hi,

I'm having some trouble with a code I'm currently working on and I would really appreciate some help.

What I'm trying to do is the following:

I have a range (A1:A50), each cell populated by a string value.
The vba code is looping through all the range cells and if a specific value is found it loads up a User Form.

The User Form contains a ComboBox with a list of options and an OK button.

What I'm trying to do is, when the form is loaded I want to choose an option (value) from the combobox and when I hit the ok button, the value I selected in the combobox, to replace the value of the current cell (the one that triggered the form load event)

I find this difficult because I can't manage to get the new value of the combo box (_AfterUpdate) to the module where my code is.

Thanks for the help!

 

[MickG]

This is first, my basic code to Open the Userform and Pass the Specific string (As an Object ) to the Userform
Nb:- My column "A" has the strings "Data1 to "Data20" in it.
Nb:- Use the necessary bits to suit.

Private Sub CommandButton2_Click()
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For Each Dn In Rng
    If Dn = "data10" Then
        Call UserForm2.vars(Dn)
        UserForm2.Show
    End If
Next Dn

This code to Go in the Userform Module:-

Option Explicit
Dim nOb As Object
Sub vars(ByRef Ob As Object)
    Set nOb = Ob
End Sub


Private Sub ComboBox1_Change()
    nOb.Value = ComboBox1.Value
End Sub

This code then Pastes the selected Combobox item in the chosen Cell in column "A".

 

[jadox]

The command button is inside the form, there is none in the sheet. I use the OK (is not a vbOK) button only to unload the form, so the command button code is not working for me

 

[MickG]

What I'm trying to do is, when the form is loaded I want to choose an option (value) from the combobox and when I hit the ok button, the value I selected in the combobox, to replace the value of the current cell (the one that triggered the form load event)

How do you load (What code) the "Userform", in relation to your "Current cell" and the "Triggering event ".

 

[jadox]

the range loop I was mentioning is part of a larger code in a module, it's a subroutine. So that's why I want to change the cell values at a specific point in the process and move on with some other workbook processes.
Module 1
code.........
code........
code.......
For Loop (triggering the User form to change some cell values)
End Loop
code.........
code.........
etc.
End Sub

 

[MickG]

Place the relevant bit of code in your Module as below:-

Module 1
code.........
code........
code.......
For Loop (triggering the User form to change some cell values)
'In here when the criteria is met you need the following code, for your Userform
Call UserForm2.vars(Dn) ' Nb:- Dn refers to the Range Object when your criteria is met, like Cell(c,1):- NB not the Value , the Object !!!!!
        UserForm2.Show
End Loop
code.........
code.........
etc.
End Sub