cell selection in vba

[red95]

is there a way to select cell a1 in vba

 

[gsistek]

Range("A1").Select

 

[red95]

here is my code to trouble is it does not always put the data in the next cell any ideal


Private Sub cmdAdd_Click()
Range("A1").Select

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("circulationstar")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Must At Least Enter a 0 In 010-104"""
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
ws.Cells(iRow, 3).Value = Me.TextBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox4.Value
ws.Cells(iRow, 5).Value = Me.TextBox5.Value
ws.Cells(iRow, 6).Value = Me.TextBox6.Value
ws.Cells(iRow, 7).Value = Me.TextBox7.Value
ws.Cells(iRow, 8).Value = Me.TextBox8.Value
ws.Cells(iRow, 9).Value = Me.TextBox9.Value
ws.Cells(iRow, 10).Value = Me.TextBox10.Value
ws.Cells(iRow, 11).Value = Me.TextBox11.Value
ws.Cells(iRow, 12).Value = Me.TextBox12.Value
ws.Cells(iRow, 13).Value = Me.TextBox13.Value
ws.Cells(iRow, 14).Value = Me.TextBox14.Value
ws.Cells(iRow, 15).Value = Me.TextBox15.Value
ws.Cells(iRow, 16).Value = Me.TextBox16.Value
ws.Cells(iRow, 17).Value = Me.TextBox17.Value
ws.Cells(iRow, 18).Value = Me.TextBox18.Value
ws.Cells(iRow, 19).Value = Me.TextBox19.Value
ws.Cells(iRow, 20).Value = Me.TextBox20.Value
ws.Cells(iRow, 21).Value = Me.TextBox21.Value
ws.Cells(iRow, 22).Value = Me.TextBox22.Value
ws.Cells(iRow, 23).Value = Me.TextBox23.Value
ws.Cells(iRow, 24).Value = Me.TextBox24.Value
ws.Cells(iRow, 25).Value = Me.TextBox25.Value
ws.Cells(iRow, 26).Value = Me.TextBox26.Value
ws.Cells(iRow, 27).Value = Me.TextBox27.Value
ws.Cells(iRow, 28).Value = Me.TextBox28.Value
ws.Cells(iRow, 29).Value = Me.TextBox29.Value
ws.Cells(iRow, 30).Value = Me.TextBox30.Value
ws.Cells(iRow, 31).Value = Me.TextBox31.Value

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.TextBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
Me.TextBox26.Value = ""
Me.TextBox27.Value = ""
Me.TextBox28.Value = ""
Me.TextBox29.Value = ""
Me.TextBox30.Value = ""
Me.TextBox31.Value = ""

Me.TextBox1.SetFocus

End Sub

Private Sub cmdmainmenu_Click()

Unload Me
circulationmenu.Show

End Sub

Private Sub UserForm_Click()

End Sub

 

[HalfAce]

The answer above will work as long as you're talking about selecting A1 of the currently active sheet.
If you want A1 of a different sheet, you'd need to activate (or even select) the sheet of interest in a separate line before selecting the cell or range. (ie.)
Sheets("Sheet2").Activate
Range("A1").Select

That being said, why do you want to select the cell? If it's for any reason other than wanting the user to physically be at A1, then you probably don't need to actually select the range in order to work with it.
If you were on sheet 3 and executed a routine like this:

Sheets("Sheet1").Range("A1").Value = "This is my new value."

it will change the value of Sheet1 A1 without ever selecting the sheet or the range in question.

Hope it helps.

 

[red95]

because i have data in a380 and it start putting the data there instead of in the next empty cell with my code can i make it so it just starts the checkat
cell 379 up

 

[HalfAce]

If I understand the code you posted, you can give this a try and see if I'm even close. If not let us know...
(Note: this replaces all the code you posted above.)

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim ThisControl As Control
Dim TxtBxNum As Long

Set ws = Worksheets("circulationstar")

Range("A1").Select 'Is this really necessary?

'''find first empty row in column A of "circulationstar" sheet
iRow = ws.Cells(Rows.Count, 1).End(xlUp)(2).Row

'''check for a part number (checks if textbox 1 is empty)
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Must At Least Enter a 0 In 010-104"
Exit Sub
End If

'''copy the data to the database (to next row of column A)
TxtBxNum = 1
For Each ThisControl In Me.Controls
  If TypeOf ThisControl Is MSForms.TextBox Then
    ws.Cells(iRow, TxtBxNum).Value = ThisControl.Value
    ThisControl.Value = ""
    TxtBxNum = TxtBxNum + 1
  End If
Next ThisControl
Unload Me
End Sub

Private Sub cmdmainmenu_Click()
Unload Me
circulationmenu.Show
End Sub

Hope it helps.

 

[red95]

thanks that works

 

[HalfAce]

welcome. figured it would.