Userform code not posting changes to col C(3) of sheet

[chazrab]

Clicking on the Update button in the image below, this code only posts changes to Textbox1. For some reason it doesn't post any changes made to Textbox2(below Textbox1 on rhe userform.

Private Sub cmdUPDATE_Click()
Dim ws As Worksheet
Dim r As Variant
r = ListBox1.ListIndex '----> current selected item and(sheet row) of Listbox1
If r = 0 Then
r = r + 1
Else
End If
Set ws = Worksheets("THEEAST")
  With wst
    ws.Cells(r, 2).Value = Me.TextBox1.Text ---> rhis line posts code successully to col B(2) of the sheet
    ws.Cells(r, 3).Value = Me.TextBox2.Text '--->this line does not post changes to col C(3) for some reason
End With
End Sub

If I comment out the first ws line above, ( ws.Cells(r, 2).Value = Me.TextBox1.Text ),
the code runs the second ws line and changes are posted to col C(3) which is Textbox2.
I've used this same code successfully many times, with textboxes on a userform, but NOT with a listbox. It's as if something quirky is going on
I should be able to make changes to BOTH textboxes on the form and have them reflected back to the sheet in their proper cols.

Can someone please help me figure out what's happening and not happening here?
Thanks for anyone's help. I'm sure its something simple. This has worked many times.
cr

 

[DanteAmor]

Try this:

Private Sub cmdUPDATE_Click()
  Dim r As Long
  Dim sh As Worksheet
  
  If ListBox1.ListIndex = -1 Then
    MsgBox "Select an item from listbox"
    Exit Sub
  End If
  r = ListBox1.ListIndex + 1
  Set sh = Sheets("THEEAST")
  sh.Range("B" & r).Value = TextBox1.Value
  sh.Range("C" & r).Value = TextBox2.Value
End Sub

 

[chazrab]

Hi Dante. Copied your code exactly as above. Still not posting to Textbox2. See new images below and test wkbk. Seems like
the code for Textbox1 entry is blocking updating Textbox2. Never had this happen b4.

Thanks for helping.
cr

TESTWKBK.xlsm

Sheet1 Oranges,Oranges. Textbox1. More text. ,Orange Apples,And this is apples,Red grapes,This grapes,Purple Figs,Figs are good, also,Brown Pears,Pears, also very good,Green

docs.google.com

 

[DanteAmor]

I downloaded your TESTWKBK file, but I can't open it, because of your file version.

You can put ALL your code here.

 

[chazrab]

[B]code for Listbox1:[/B]
Private Sub ListBox1_Click()
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox3.Value = ListBox1.ListIndex
Me.TextBox1.SetFocus
Me.TextBox1.SelStart = 0
End Sub
[B]code for update button which is your code:[/B]
Private Sub cmdUPDATE_Click()
  Dim r As Long
  Dim sh As Worksheet
    If ListBox1.ListIndex = -1 Then
    MsgBox "Select an item from listbox"
    Exit Sub
  End If
  r = ListBox1.ListIndex + 1
  Set sh = Sheets("THEEAST")
  sh.Range("B" & r).Value = TextBox1.Value
  sh.Range("C" & r).Value = TextBox2.Value [B]----->this line is not updating values in col C on row r.  Changes not posted to the sheet.  It is left unchanged[/B]
End Sub
This is the entire code that performs sheet updating in cols B and C from  Listbox1 and two textboxes 1 and 2 on a userform.  
Any userform with this code and controls should work on a test sheet and give the exact same result.
Let me know what you find out.  Weird. 
cr

 

[DanteAmor]

Note: To highlight lines in the code use the Rich Code Tag:

Test the following:

Private Sub cmdUPDATE_Click()
  Dim r As Long
  Dim sh As Worksheet
  
  If ListBox1.ListIndex = -1 Then
    MsgBox "Select an item from listbox"
    Exit Sub
  End If
  r = ListBox1.ListIndex + 1
  Set sh = Sheets("THEEAST")
  sh.Range("B" & r).Value = TextBox1.Value
  sh.Range("C" & r).Value = "Some text to try"
End Sub

 

[chazrab]

Doesn't work. Same result.

 

[DanteAmor]

Do you have more code or some other macro running in the sheet events?

Try to do it in a new book, on a new sheet. create the userform, paste the code and try again.

 

[MitchellNicholls]

I tried your code and it works fine - I made a small change

Change Sheet name in bold

Dim ws As Worksheet
Dim r As Variant
r = ListBox1.ListIndex '----> current selected item and(sheet row) of Listbox1
MsgBox ListBox1.ListIndex
r = r + 1

Set ws = Worksheets("Sheet1")
  With ws
    ws.Cells(r, 2).Value = Me.TextBox1.Text '---> rhis line posts code successully to col B(2) of the sheet
    ws.Cells(r, 3).Value = Me.TextBox2.Text '--->this line does not post changes to col C(3) for some reason
End With

 

[chazrab]

Dante, Mitchell, see this: "some text to try" will of course populate in Textbox2 because you assigned it that value in quotes as copied your code below.
Comment it out, as I did below, add Textbox2.value in front run the form, enter anything in Textbox1 and Textbox2, click the Update button and tell me if what you entered is
(1) displayed in both Textbox1 and Textbox2 on the form and (2) simultaneously gets entered into col C on the same row r.
If it works for you, then the sheet must be corrupted or something else not obvious is going on.
Here's what I did:

Private Sub cmdUPDATE_Click()
  Dim r As Long
  Dim sh As Worksheet
    If ListBox1.ListIndex = -1 Then
    MsgBox "Select an item from listbox"
    Exit Sub
  End If
  r = ListBox1.ListIndex + 1
  Set sh = Sheets("THEEAST")
  sh.Range("B" & r).Value = TextBox1.Value
  sh.Range("C" & r).Value = TextBox2.Value '"Some text to try"------->Textbox2.value
End Sub

images show new comments not posting to the sheet. I've included the new test workbook just in case you can open it.

TESTWKBK.xlsm

Sheet1 Dallas,TRY1. Weak offense,Some text to try Tampa Bay,Can't stand Brady,I like team colors KC,Patrick M big on play action,I think they will win Super Bowl Pittsburgh,Weak offense,Inconsistent Arizona Cardinals,Love the team,Would like to see them win SB

docs.google.com

cr