TextBox value to worksheet cell info

[ipbr21054]

Hi,

Code in use is shown below.

I am trying to place the value into my worksheet at A34 BUT the valueskeeps going to cell E1

Private Sub TransferButton_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 2
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT ALL OPTIONS", vbCritical, "MONTH,YEAR & MILEAGE TRANSFER MESSAGE"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.TextBox1)
    
    With ThisWorkbook.Worksheets("MILEAGE")
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(1, i + 3) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(1, i + 2) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i
    
    End With
    [COLOR=rgb(184, 49, 47)]TextBox1.Value = Worksheets("MILEAGE").Cells("34,1")[/COLOR]
    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    
    MsgBox "Month,Year & Mileage Have Been Updated", vbInformation, "SUCCESSFUL MILEAGE MESSAGE"
    Unload MYFMILEAGE
    
End Sub

 

[gallen]

the value in the 'Cells' section shouldn't be a string:

.Cells(34, 1)

 

[ipbr21054]

Hi,
That still enters the cell E1

 

[ipbr21054]

HI,
No matter what i enter 34, 1 22,5 8, 3 the value always goes into cell E1 ?

 

[gallen]

Apologies, the line I mentioned doesn't write info to the cells it gets data from.

This code below is the code that writes information to the worksheet and it seems muddled. At least it's difficult to read.


The Ubound of ControlsArr wil always be 2 (0, 1 and 2 to represent the 3 controls added to the array) so why loop from 0 to the ubound?

Go through each line here and write in english (comment) what each one should do and I'm sure you'll see the issue

 ControlsArr = Array(Me.ComboBox1, Me.ComboBox2, Me.TextBox1)
    With ThisWorkbook.Worksheets("MILEAGE")
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(1, i + 3) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(1, i + 2) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
    Next i

 

[gallen]

Also none of those lines will write to A34 ever....

Is this line the wrong way round?

    'this line writes the value FROM cells A34 to the text box. _
    Should it be Worksheets("MILEAGE").Cells("34,1") = TextBox1.Value?
    TextBox1.Value = Worksheets("MILEAGE").Cells("34,1")

 

[ipbr21054]

Hi,
The Combobox from userform to worksheet work fine.

I now need to get value from textbox1 on userform to worksheet cell A34

 

[ipbr21054]

HI,

Please use this code and forget the above.

Private Sub TransferButton_Click()
    Dim i As Integer
    Dim ControlsArr As Variant, ctrl As Variant
    Dim x As Long
    For i = 1 To 2
       With Me.Controls("ComboBox" & i)
            If .ListIndex = -1 Then
                MsgBox "MUST SELECT BOTH OPTIONS", 48, "MONTH & YEAR TRANSFER MESSAGE"
                .SetFocus
                Exit Sub
            End If
        End With
    Next i
    
    ControlsArr = Array(Me.ComboBox1, Me.ComboBox2)
    
    With ThisWorkbook.Worksheets("MILEAGE")
        For i = 0 To UBound(ControlsArr)
         Select Case i
            Case 1, 2, 4
               .Cells(1, i + 3) = IIf(IsNumeric(ControlsArr(i)), Val(ControlsArr(i)), ControlsArr(i))
            Case Else
               .Cells(1, i + 2) = ControlsArr(i)
               ControlsArr(i).Text = ""
         End Select
             Next i
    End With

    ActiveWorkbook.Save
    Application.ScreenUpdating = True
    
    MsgBox "Month & Year Have Been Updated", vbInformation, "SUCCESSFUL MESSAGE MILEAGE"
    Unload MYFMILEAGE
    
End Sub

Now we need to take value from userform Textbox1 to worksheet cell A34

 

[gallen]

So wouldn't just adding this line before the code ends do as you ask:

ThisWorkbook.Worksheets("MILEAGE").Range("A34") = Me.Textbox1.Text

 

[ipbr21054]

Show me where in my code to insert it as it didn’t work for me