Userform Combobox list replace selected value with value from inputbox

[matija385]

Hi,

I have userform4 and combobox1 that is filled from range that is in Table28. What i would like to do is possibility to change values inside that Table28.

For example:

Table28 values are:

XXX
ZZZ
YYY
WWW

So, when i select XXX value in combobox, i get inputbox with question "Enter text to replace XXX", i enter desired text (for ie AAA) and when i click OK, value XXX in Table28 is replaced with my desired text (entered in inputbox, in this example AAA).

I quite new to vba, so i would very much appreciate some help.

Thank you,

Matija

 

[mrmmickle1]

So I'm going to assume:

ComboBox 1
RowSource = MyTable!A2:A7

Table 28 =

Book1
	A
1	Car Make
2	Honda
3	Acura
4	Toyota
5	MyCarLine
6	BMW
7	Jaguar
MyTable

Then use this code in the UserForm:

[COLOR=#0000ff]Private Sub[/COLOR] ComboBox1_Change()


[COLOR=#0000ff]    Dim[/COLOR] MyVar [COLOR=#0000ff]As String[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]cboIndex [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] myNewVal [COLOR=#0000ff]As String[/COLOR]


    MyVar = Me.ComboBox1.Value
    cboIndex = Me.ComboBox1.ListIndex
    
    myNewVal = InputBox("Enter New Value!")
    Sheets("MyTable").Range("A" & cboIndex + 2) = myNewVal
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[matija385]

Thanks Matt,

works like a charm. How would i upgrade code to see if value entered in inputbox already exists in my range and if it does msgbox should popup saying that value already exists and doesn't change anything? Let's say, protection for creating duplicates in range... of course, if value entered in inputbox doesn't exist in same range, code should do what it does now

Also, could you please explain me "+2" in last row in code, what does it mean?

Thank you,
Matija

 

[mrmmickle1]

Try using this code to account for duplicates:

[COLOR=#0000ff]Private Sub[/COLOR] ComboBox1_Change()


[COLOR=#0000ff]    Dim [/COLOR]MyVar [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] cboIndex [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]myNewVal [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] myNewValRng [COLOR=#0000ff]As[/COLOR] Range


    MyVar = Me.ComboBox1.Value [COLOR=#008000]'Get combobox value[/COLOR]
    cboIndex = Me.ComboBox1.ListIndex [COLOR=#008000]'Get combobox Item Index[/COLOR]
    
InputLabel:
    myNewVal = InputBox("Enter New Value!")[COLOR=#008000] 'Get New Value[/COLOR]
    
[COLOR=#008000]'Look in Range for newly entered value[/COLOR]
[COLOR=#0000ff]    Set [/COLOR]myNewValRng = Columns(1).Find(What:=myNewVal, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=[COLOR=#0000ff]False[/COLOR], _
                    SearchFormat:=[COLOR=#0000ff]False[/COLOR])
 [COLOR=#0000ff]   If Not [/COLOR]myNewValRng [COLOR=#0000ff]Is Nothing Then[/COLOR]
        MsgBox "The value already exists.....try again", vbCritical, "You're Not Paying Attention..."[COLOR=#008000] 'If it already exists start the process again....[/COLOR]
     [COLOR=#0000ff]   GoTo[/COLOR] InputLabel 
[COLOR=#0000ff]    Else[/COLOR]
        Sheets("MyTable").Range("A" & cboIndex + 2) = myNewVal [COLOR=#008000]'Input Value onto sheet[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[matija385]

Hi Matt,

Thank you for the code, but i tested it, but even though i enter value that already exists in my range, code overwrites selected value with duplicate that already exists in range.

My code is:

Private Sub ComboBox1_Change()


    Dim MyVar As String
    Dim cboIndex As Integer
    Dim myNewVal As String
    Dim myNewValRng As Range


    MyVar = Me.ComboBox1.Value 'Get combobox value
    cboIndex = Me.ComboBox1.ListIndex 'Get combobox Item Index
    
InputLabel:
    myNewVal = InputBox("Enter New Value!") 'Get New Value
    
'Look in Range for newly entered value
    Set myNewValRng = Columns(1).Find(What:=myNewVal, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
    If Not myNewValRng Is Nothing Then
        MsgBox "The value already exists.....try again", vbCritical, "You're Not Paying Attention..." 'If it already exists start the process again....
        GoTo InputLabel
    Else
        Sheets("Administracija").Unprotect
            Sheets("Administracija").Range("A" & cboIndex + 2) = myNewVal
                
        Sheets("Administracija").Protect
        Sheets("Azuriranje").Range("E16").Select
    
    End If


End Sub

Sorry if i bother you, but is it possible instead of inputbox to you textbox? My userform would then look like:

Select value: Combobox1
Enter new value: Textbox1

Button Cancel Button Replace

And then i would from Combobox1 select value which i would like to change, in Textbox1 i would enter new value, and the change would be done when i hit button Replace. If i hit button Cancel, it would exit sub. Also, my range is named range Table28 (created in excel with Insert > Table, and then named with Define name Table28), how could i implement this, because i have many ranges that i use, and would use this macro on several other ranges.

Sorry for changes i said now, i just realized this would be quite easier for end user.

Kind regards,
Matija

 

[mrmmickle1]

Try adding a commandbutton to execute changes to your form:

I have no issue with duplicates. I was unable to reproduce this error. Please look for leading or trailing spaces and/or hidden characters in your text.

[COLOR=#0000ff]Private Sub [/COLOR]CommandButton1_Click()


    [COLOR=#0000ff]Dim[/COLOR] MyVar [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]cboIndex [COLOR=#0000ff]As Integer[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]myNewVal [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] myNewValRng [COLOR=#0000ff]As[/COLOR] Range


    MyVar = Me.ComboBox1.Value [COLOR=#008000]'Get combobox value[/COLOR]
    cboIndex = Me.ComboBox1.ListIndex [COLOR=#008000]'Get combobox Item Index[/COLOR]
    myNewVal = Me.TextBox1.Value[COLOR=#008000] 'Get New Value[/COLOR]
    
[COLOR=#008000]    'Look in Range for newly entered value[/COLOR]
[COLOR=#0000ff]    Set[/COLOR] myNewValRng = Columns(1).Find(What:=myNewVal, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=[COLOR=#0000ff]False[/COLOR], _
                    SearchFormat:=[COLOR=#0000ff]False[/COLOR])
 [COLOR=#0000ff]   If [/COLOR]Me.TextBox1.Value = vbNullString [COLOR=#0000ff]Then[/COLOR]
        MsgBox "Please enter a value....", vbCritical, "You're Not Paying Attention..." [COLOR=#008000]'TextBox1 is empty[/COLOR]
        Me.ComboBox1.Value = vbNullString
        Me.TextBox1.Value = vbNullString
    [COLOR=#0000ff]    GoTo[/COLOR] FinishUp
   [COLOR=#0000ff] ElseIf Not[/COLOR] myNewValRng[COLOR=#0000ff] Is Nothing Then[/COLOR]
        MsgBox "The value already exists.....try again", vbCritical, "You're Not Paying Attention..." [COLOR=#008000]'If it already exists start the process again....[/COLOR]
        Me.ComboBox1.Value = vbNullString
        Me.TextBox1.Value = vbNullString
     [COLOR=#0000ff]   GoTo[/COLOR] FinishUp
[COLOR=#0000ff]    Else[/COLOR]
        Sheets("Administracija").Unprotect
        Sheets("Administracija").Range("A" & cboIndex + 2) = myNewVal
                
        Sheets("Administracija").Protect
        Sheets("Azuriranje").Range("E16").Select
    
[COLOR=#0000ff]    End If[/COLOR]
    
FinishUp:


[COLOR=#0000ff]End Sub[/COLOR]

 

[matija385]

Thank you, this works with textbox.

When i looked in my table, the value i changed has ' in front of changed value in formula bar like:

'AAA
AAA
BBB
CCC

I don't have no other characters or spaces.

 

[matija385]

Also, i have to ask, if i have two tables named Table1 and Table2 and their ranges are Table1 A1:A7 and Table2 A20:A30, how would i define ranges in

 Sheets("Administracija").Range("A" & cboIndex + 2) = myNewVal

Also, with Colums(1), is it refering to column A? Can it be done to find/look in named range, for example Table3?

Set myNewValRng = Columns(1).Find(What:=myNewVal, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)

I'm asking because if i move table to different column or rows, will i have to change macro as well for different range?

I was trying with name of my table "Table3", but it's not working, it's giving me run-time error 1004, Application-defined or object-defined error

Sheets("Administracija").Range("Table28" & cboIndex + 2) = myNewVal

 

[mrmmickle1]

'AAA
AAA
BBB
CCC

'AAA is not the same as AAA.... if you want to account for values that are similar you will need to use this

LookAt:=xlPart, _

I'm asking because if i move table to different column or rows, will i have to change macro as well for different range?

Yes this is true

Sheets("Administracija").Range("Table28" & cboIndex + 2) = myNewVal

You get an error because there is no Column named Table28....

Can you please use the HTML Maker to paste the structure of your data? What columns are you housing your data?

 

[matija385]

Hi Matt,

Can I send you private message with link where you can download xlsm file along with one .doc file where i tried to explain everyting?

Thnx,

Kind Regards,
Matija