Help with user input.

[sramelyk]

I have an excel sheet for entering inventory. I have created a dialog box for user input for rapid entry. I have a problem though. If I clear the worksheet of all data and enter more data using the dialog box it doesn't enter it in the upper most empty cell. If something gets deleted I want the newest information to be entered in the uppermost available cell. I can attach the code if needed. I am definitely new to VB.

 

[Bong]

You don't say how you're finding the available cell (row) but have you tried: Range("a65536").End(xlUp).Row? You would then need to see if the cell has a value and if so, add 1.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

Chances are, your VBA code is referencing the UsedRange property. Use Bob's suggestion instead.

Skip,
[sub]
[red][/red]
[/sub]

 

[sramelyk]

Here is my code.. I am very inexperienced with VBA..

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("louisvilleinv")

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

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtEquipment.Value
ws.Cells(iRow, 2).Value = Me.txtModel.Value
ws.Cells(iRow, 3).Value = Me.txtSerial.Value
ws.Cells(iRow, 4).Value = Me.txtCustomerName.Value
ws.Cells(iRow, 5).Value = Me.txtComment.Value

'clear the data
Me.txtEquipment.Value = ""
Me.txtModel.Value = ""
Me.txtSerial.Value = ""
Me.txtCustomerName.Value = ""
Me.txtEquipment.SetFocus

End Sub

 

[sramelyk]

Where would I insert the particular code you are referring to?

 

[Bong]

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

would become

iRow = ws.range("a65536").End(xlUp).Row
if not cells(iRow,1).value = "" then iRow=iRow+1

_________________
Bob Rashkin

 

[sramelyk]

Its strange.. It made no difference. The first empty cell is A6 which is the first cell on the spreadsheet.. It always puts the information in A17 if there is nothing there.. That to me doesn't make sense.

 

[PHV]

How was cleared/deleted A6:A16 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sramelyk]

I tried a couple of different ways after your post.. Highlighing every cell then hitting delete and also just hitting space bar.. Now its starting in a18.. I can delete a18 and it starts there again.

 

[Bong]

What happens if you choose Clear Contents on the highlit A6:A16?

_________________
Bob Rashkin

 

[PHV]

What about this in the debug window ?
Worksheets("louisvilleinv").Rows("6:18").Delete
? Worksheets("louisvilleinv").UsedRange.Address

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"...and also just hitting space bar..."

WHAT?????

A SPACE is a CHARACTER, just a valid as any other character.

A SPACE is NOT a DELETE button!!!

Skip,
[sub]
[red][/red]
[/sub]

 

[sramelyk]

Clear contents definitely worked. I can handle this just fine.. But I am afraid other user will just delete the information.. No way around this?

I KNOW SPACE IS A CHARACTER.. But I am making this for several users and was just trying what I believe others would try.

 

[Bong]

Just for laughs, if the cell is not blank, what would you expect to be in it?

_________________
Bob Rashkin

 

[sramelyk]

Nothing.. For example.. I I have the first 20 entries already there.. And I delete one of them.. I would expect that when I enter new info in the dialog box it would put the information in that new available cell.. Does that makes sense?

 

[Bong]

No, what I mean is, what is in the cell, in column A, that has valid data in it?

_________________
Bob Rashkin

 

[sramelyk]

Whatever I enter in the dialog box.. Or in the cell directly.. Sometimes while experimenting just random characters..

maybe I don't understand.

 

[Bong]

Never mind, it was just a thought. There's really no way for a macro to distinguish between a cell that has the value, " ", and one that has any other value without crawling through each cell and looking at the value. However, highlighting a range of cells and pressing the delete key, at least on my PC, causes range("a6500").End(xlUp).Row to change value correctly (when those cells are in column A and include the last cells in the column).

_________________
Bob Rashkin