Posts by Boxxcar

1. Having difficulty using countif on a SalesAnalysis worksheet to count the number of sales per year by looking at the Year in the Date Sold column on the Ebay inventory sheet,

2. On the Ebay Inventory sheet, having difficulty creating a countif formula in the Date Sold column in a way that allows adding new rows of data without having to change the formula.

3. On the Ebay Inventory sheet, rows 889-894, don't uderstand why #VALUE! appears but not in the rows before and after with the same formula.

Insight will be appreciated.

Thanks! Now it works. Appreciate the input.

I have a spreadsheet where I want rows 1-3 as a header and data rows start with row 4. I want rows 1-3 to remain visible as I page down thru rows 4 and following. I want to be able to sort rows 4 and following with rows 1-3 not getting sorted. I have tried multiple online suggestions but have not been able to make them work.

Help will be appreciated.

Thanks for checking.

Got the resend and the have opened the form.

I downloaded SumTextBoxes.xlsm opened it and opened the code window. I see a single sheet named Sheet. Nothing on the sheet, nothing in the code window. My inexperience as a novice is showing. What am I not understanding?

On a Userform I have a textbox77 whose value needs to be the sum of the value in textbox73 minus the value in textbox76 and change whenever the user changes the value in either textbox73 or textbox76. I am unable so far to write code that doesn't throw an error.

I am a self taught excel code writer and benefit greatly from this forum. Any help on what I (mistakenly) thought would be a simple piece of code will be appreciated.

I am following a suggestion to start over with my form and make it multipage.

On page one the TabIndex settings work and the cursor tabs through the textboxes in the desired order. On page 2 I have two CommandButtons and 20 textboxes. I have set the TabIndex numbers to tab from textbox1 to commandbutton2, to textbox49, to commandbutton11, to textbox50 through textbox67.

However, the cursor tabs from from textbox1 to commandbutton2, FROM COMMANDBUTTON2 TO TEXTBOX 50, skipping textbox49 and commandbutton11. I have tried to intercept the tab key press on commandbutton 2 with the following code to force the cursor to move to textbox49.

Private Sub CommandButton2__KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)


If KeyCode = vbKeyTab Then
Me.textBox49.Activate
End If


End Sub

However, when I step through the code, this Sub is not accessed and the cursor does not go to textbox49. If I move the cursor to textbox49, then click Tab, the cursor moves to textbox50. If I move the cursor to commandbutton11, then click Tab, the cursor moves to textbox50.

I will happily attach code if that will be helpful.

Suggestions will be appreciated.

My form allows a user to add a new record to the existing records on a worksheet or to edit an existing record present on one of the worksheets. Several of the columns across the sheets are named the same, some are not. I created the one form to present to the user with a command button for each of the sheets. Based on user's button push, I make visible the appropriate textboxes. I want to have the user use the Tab key to move through the textboxes in a specific order. I know how to set the TabStop = True. I know to enter a number for the TabIndex for the sequence across the textboxes. Therefore I need a make visible a different set of textboxes for the user's input and need to change the TabIndex of those fields to match the specific order I want. I have tried several ways but am not yet able to succeed. The results of my efforts seem to create a random tab sequence rather than the one I want. My current hypothesis is I may have to reset the TabStop and TabIndex for every textbox after the user selects a command button. BetaVersionTest.xlsmBetaVersionTest.xlsm. Help, suggestions, will be appreciated.

Need to change form tab order based on the commandbutton user selects. 5 commandbuttons, 30+ textboxes

can I change the tab order programatically? I want the order to change to the sequence that is the most natural for the different functions the user is following at different steps in the process

That is a better approach. I just didn't think of it. I have not used very many forms before and they were not very complicated. I will give it a go.

Thanks for the question and the suggestion. Didn't realize the sheet would be updated as well as the ListBox.

Give the user a list of product names that include what user entered in the form. When the user sees the desired product in the List Box, they can click on the Go To button the form will close and user will be taken to the record on the datasheet to edit the record.

Search Database.xls

That does help. And I hope I have attached my sample workbook, I hope successfully. I absolutely want to be a well mannered participant. This is my first forum experience. I find the content interesting and helpful.

Boxxcar...Trying to get hitched to the train...

I apologize, I reread the rules just now. Clearly I am missing something. Maybe this is it. I thought I had to type CODE before and END CODE after. Now I think I see it, I have to click the </> symbol in the menu. Is that it? If it is not, please forgive my lack of experience and or receptivity and walk me through it with baby steps.

I have been able to adapt Leith's code to find appropriate records in the sheet. I have added a "GoTo" button, CommandButton 5. I want the "GoTo" button to send the user to the found record in the spreadsheet so user can edit the record. I've tried to send the address to a Sub that activates the found record in edit mode. So far, none of my attempts have worked. Assistance appreciated.

CODE

'Written: January 17, 2009
'Author:  Leith Ross
'Summary: This Form allows the user to search column "A - M" of the active sheet.
'         Seacrh options include match case, partial or whole word matching, and
'         finding the first occurance or occurances of a search term. All column
'         values are returned for the row of the successful search or searchs.
'         The user can navigate through the records using the "Back" and "Next"
'         buttons for faster viewing.




Dim SearchRecords As Long


Private Sub CheckBox2_Change()
'MATCH WHOLE WORD
If CheckBox2.Value = True Then
CheckBox2.Tag = "1"
Else
CheckBox2.Tag = "2"
End If
End Sub


Private Sub CommandButton2_Click()
'SEARCH


StartRow = 2


Col = ComboBox1.ListIndex + 1
If Col = 0 Then
MsgBox "Please choose a category."
Exit Sub
End If


If TextBox1.Text = "" Then
MsgBox "Please enter a search term."
TextBox1.SetFocus
Exit Sub
End If


TextBox1 = TextBox1 & "*"


LastRow = Cells(Rows.Count, Col).End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)


Set Rng = Range(Cells(2, Col), Cells(LastRow, Col))


Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
After:=Rng.Cells(1, 1), _
LookAt:=CInt(CheckBox2.Tag), _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=CheckBox1.Value)


If Not FoundMatch Is Nothing Then
FirstAddx = FoundMatch.Address
ListBox1.Clear


Do
Cnt = Cnt + 1
R = FoundMatch.Row
ListBox1.AddItem "     Row " & R
'For Each C In Range(Cells(R, "A"), Cells(R, "M"))
For Each C In Range(Cells(R, "E"), Cells(R, "E"))
ListBox1.AddItem C.Text
Next C
Set FoundMatch = Rng.FindNext(FoundMatch)
A = FoundMatch.Address
Loop While CheckBox3 = True And FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
Label1.Caption = "Matches =" & Str(Cnt)
SearchRecords = Cnt
Else
ListBox1.Clear
Label1.Caption = ""
SearchRecords = 0
MsgBox "No match found for " & TextBox1.Text
End If


End Sub


Private Sub CommandButton3_Click()
'BACK ONE SEARCH RECORD
Dim CntDown As Long


If SearchRecords <> 0 Then
Select Case ListBox1.ListIndex
Case Is < 13
ListBox1.TopIndex = 0
ListBox1.ListIndex = 0
Case Is > 13
CntDown = ((ListBox1.ListIndex \ 14) * 14) - 14
ListBox1.TopIndex = CntDown
ListBox1.ListIndex = CntDown
End Select
End If


End Sub


Private Sub CommandButton4_Click()
'NEXT SEARCH RECORD


Dim CntDown As Long


If SearchRecords <> 0 Then
Select Case ListBox1.ListIndex
Case Is < 14
ListBox1.TopIndex = 14
ListBox1.ListIndex = 14
Case Is < ListBox1.ListCount - 14
CntDown = ((ListBox1.ListIndex \ 14) * 14) + 14
ListBox1.TopIndex = CntDown
ListBox1.ListIndex = CntDown
End Select
End If


End Sub


Private Sub CommandButton5_Click()


'Leave form and Go To the selected record in the active sheet


'GoToRecord (?) = the record showing in ListBox1


End Sub


Sub GoToRecord(Found)


MsgBox "Found at address " & Found


End Sub






Private Sub UserForm_Activate()


AddToForm MIN_BOX
AddToForm MAX_BOX


With ComboBox1
'For I = 1 To 13
For I = 1 To 5
.AddItem Cells(1, I)
Next I
.SetFocus
End With


End Sub

Thanks. That works perfectly now. I appreciate the help.

I call this Sub with a Button on a UserForm on the first worksheet. The UserForm has the user type into the text field txtItemName the name of an inventory item that is listed on “Ebay Inventory.” The code runs fine until the ‘Set rgFound’ etc line. rgFound does not have the Range where the value was found. The value is in E13 of the Ebay Inventory sheet. This error message appeared: “Run Time error ‘91’. Object variable or With block variable not set. I have tried multiple things but so far no dice. Help will be appreciated

Private Sub cmdAdd_Click(


'Find item in Ebay Inventory sheet


Dim SearchFor As String     'Find this string in Ebay Inventory


Dim iRow As Long               'last data row in Ebay Inv


Dim ws As Worksheet


Dim ws2 As Worksheet


Dim rgFound As Range






SearchFor = txtItemName






Set ws = Worksheets("Ebay Inventory")


Set ws2 = Worksheets("Poshmark Inventory")




'find first empty row in Ebay Inventory and assign it to var iRow


iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _


SearchDirection:=xlPrevious, LookIn:=xlValues).Row






Set rgFound = Range("E1:E" & (iRow)).Find(txtItemName, LookIn:=xlValues)


Debug.Print "Found " & SearchFor & "as value in: " & rgFound.Address




End Sub