MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Confused, please help

Posted by M Walker on August 16, 2001 4:00 AM


I've designed a macro that will be used to keep track of stock levels. Thanks to Ivan i can now add to the levels of stock using the following code...

rivate Sub Check(Tbx As MSForms.TextBox, sht As Worksheet, Comb As MSForms.ComboBox)
Dim curpos As Double
Dim row As Double, col As Single

curpos = Tbx.SelStart

If Comb.Text = "" Then MsgBox "No Stock selected": Exit Sub
If Not ValidateNumeric(Tbx.Text) Then
Tbx.Text = Left(Tbx.Text, curpos - 1)
MsgBox "Please use numbers only!", vbExclamation, "Invald charatcer"
row = FindPart_Row(sht, Comb.Text)
If row = 0 Then GoTo Err
If InStr(1, sht.Index, 1) > 0 Then
col = 5
col = 4
End If
Sheets(sht.Name).Cells(row, col) = Tbx.Value
End If

Exit Sub
MsgBox "A critical error has creator", vbCritical
End Sub


Function FindPart_Row(Sh As Worksheet, Str As String)
Dim topcell, bottomcell, searchRg As Range, x, Find As Range, y

With Sh
Set topcell = .Cells(3, 1)
Set bottomcell = .Cells(65536, 1)
End With

If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown)
If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp)

Set searchRg = Range(topcell, bottomcell)
x = searchRg.Address
y = Sh.Name
On Error Resume Next
Set Find = searchRg.Find(What:=Str, LookIn:= _
xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True)
If Not Find Is Nothing Then
FindPart_Row = Find.row
FindPart_Row = 0
End If
End Function

The problem is i don't understand it! I need to chnage the code slighlty so that i can remove stopck as well as add it. I don't know how this code tell assigns a textbox's contents to a cell. All i want to do is keep the same style but insert the textbox contents 1 cell to the left...

I'm very confused and running out of time (has to be finished in a few weeks!)

Any help would be appreciated. I'm using excel 97.

Thanks in advance , Matt

Posted by neo on August 16, 2001 7:44 AM


this is the line that assigns the textbox's contents to a cell:

Sheets(sht.Name).Cells(row, col) = Tbx.Value

ivan went a different way to get there than I would've but, considering what you're wanting, it's much more efficient than code I would've suggested...

either way, i think if you add a '+1' after 'col' towards the end of the line, it'll put your value in the next column over as you described :

Sheets(sht.Name).Cells(row, col + 1) = Tbx.Value

like i said, i'm not familiar with this style of coding, but it seems pretty straight forward for the most part, and this would be my guess...