Reading cells to a textbox in Excel

[jjchad]

Hi, I have created a vb form in Excel that allows me to enter data into my spreadsheet i.e. B2 to B25 but i would like to display the data stored in A2:A25. Let me explain, When the form is running i want to be able to see the data cycle through from A2:A25 in textbox2.

can anyone shed any light on this.?

Thanks John

just to make things complicated the data is formatted as HH:MM:SS

[Ecniv]

Not sure about forms in the sheet, but userforms act like normal forms in vb/access.

txtControlName.text = activesheet.cells(lngRowNumber,lngColumnNumber)

The .text may be optional.
The row and column numbers start at 1.

Depending how you scroll up and down through the sheets records, depends on whether this method is what you are looking for.

[jjchad]

Mmmm not to sure how to use that peice of code, i'm still learning vb.Here's what i have so far.

Dim lCurrentRow As Long
 
Private Sub CommandButton1_Click()
Dim LastRow As Object
lCurrentRow = lCurrentRow + 1
 
' Show contents of new row in the form:
    TextBox2.Text = Cells(lCurrentRow, 1).Value
   
          Set LastRow = Sheet4.Range("B65536").End(xlUp)
          LastRow.Offset(1, 0).Value = TextBox1.Value
          MsgBox "One code written to Optimo stop Codes"
          response = MsgBox("Do you want to enter another down time code?", _
              vbYesNo)
          If response = vbYes Then
              TextBox1.Text = ""
              TextBox1.SetFocus
          Else
              Unload Me
          End If
           ' Increment row number:
    
 
    ' Show contents of new row in the form:
    TextBox2.Text = Cells(lCurrentRow, 1).Value
    
End Sub
 
 
Private Sub UserForm_Active()
' Read initial values from Row 1:
    TextBox2.Text = Cells(1, 2).Value
    
     ' Read initial values from Row 1:
    lCurrentRow = 1
    TextBox2.Text = Cells(lCurrentRow, 1).Value
    
 
 
End Sub

So far it works the information is displayed in the textbox2 label but the formating is wrong, It's displaying 21:35:00 as 0.5416666666666.

how can i change this.?

[Ecniv]

Put format(<variable>,"dd mmm yyyy") in the code.
Or other formats... read the help file in vb on Format function.

Example:

' Show contents of new row in the form:
    TextBox2.Text = format(Cells(lCurrentRow, 1).Value,"dd/mm/yyyy")    

'---- optional
'TextBox2.Text = format(cdate(Cells(lCurrentRow, 1).Value),"dd/mm/yyyy")

Note that the format command will only work properly if the value returned is a date already (since all dates are numbers, that is the bit you currently have). So you may need to check that the value is a date first.

The cdate option is incase the cell is a string, and a valid date.

Hope they help.

[jjchad]

great it works thanks for your help.

But there's just a couple of things i need help with, 1st of all on my excel spreadsheet there's a button that starts the macro which brings up my form, how do i set the code to display the data in cell A2 as soon as the form loads.?

then when i enter my data using the textbox1 & hit the add code button, display the data in cell A3,A4,A5 & so on.?

Many thanks for your help.

John

my code so far is:

Dim lCurrentRow As Long
 
 
Private Sub CommandButton1_Click()
Dim LastRow As Object
lCurrentRow = lCurrentRow + 1
 
' Show contents of new row in the form:
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
   
          Set LastRow = Sheet4.Range("B65536").End(xlUp)
          LastRow.Offset(1, 0).Value = TextBox1.Value
          MsgBox "One code written to Optimo stop Codes"
          response = MsgBox("Do you want to enter another down time code?", _
              vbYesNo)
          If response = vbYes Then
              TextBox1.Text = ""
              TextBox1.SetFocus
          Else
              Unload Me
          End If
           ' Increment row number:
    
 
    ' Show contents of new row in the form:
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
    
End Sub
 
 
Private Sub UserForm_Active()
' Read initial values from Row 1:
    TextBox2.Text = Format(Cells(2, 1).Value, "hh:mm:s")
    
     ' Read initial values from Row 1:
    lCurrentRow = 2
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
    
 
 
End Sub
 
Private Sub UserForm_Click()
 
End Sub

[Ecniv]

Cell A2 is row 2 column1.
So if you declare a variable just under the top of the forms module, to hold the output row, and set it to 2 on the form_open event. Then where you write to the sheet, you just use that variable to point to the row. It doesn't have to be set to 2, which it would reset to each time. You could set it to the next available row space using the method already in your code, and store that as the starting point...

If you want the results to run immediately, put an open form event and call the button click
Something like this... may work.

private sub UserForm_Open()
    CommandButton1_Click
    lngOutputRow = 2
end sub

[jjchad]

sorry you've lost me.! any chance of posting the code.?i'll keep trying thou, going home soon at 3pm, so may be post tomorrow.
Thanks once again for your help.

John

[Ecniv]

Option Explicit
private lCurrentRow As Long
private lngPrintAtRow as long


Private Sub CommandButton1_Click()
Dim LastRow As Object
lCurrentRow = lCurrentRow + 1

' Show contents of new row in the form:
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
   
          Set LastRow = Sheet4.Range("B65536").End(xlUp)
          LastRow.Offset(1, 0).Value = TextBox1.Value
          MsgBox "One code written to Optimo stop Codes"
          response = MsgBox("Do you want to enter another down time code?", _
              vbYesNo)
          If response = vbYes Then
              TextBox1.Text = ""
              TextBox1.SetFocus
          Else
              Unload Me
          End If
           ' Increment row number:
    

    ' Show contents of new row in the form:
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
    
End Sub


Private Sub UserForm_Active()
' Read initial values from Row 1:
    TextBox2.Text = Format(Cells(2, 1).Value, "hh:mm:s")
    
' Read initial values from Row 1:
    lCurrentRow = 2
    TextBox2.Text = Format(Cells(lCurrentRow, 1).Value, "hh:mm:ss")
    
End Sub

Private Sub UserForm_Open()
   lngprintatrow = 2
   
End Sub

To store to the sheet....

   activesheet.cells(lngprintatrow,1) = textBox2

You should change the names of the controls to be more meaningful...