Replies

don’t know why I didn’t think of that…. thanks buddy.

thank you again for your time and knowledge. As you can tell I’m not that good. Heck I just dabble in it. So any and all help is appreciated.

ok guy he it all is gentleman all of it in it’s entirety. If you see anything that you think you can achieve better please by all means alter the code and let me know. Because time is running out here. thank you all for your time and efforets it is greatly appreciated.

yes , but I thought you had to list each problem seperatley

can you assist on my last problem please, i get that done and thats it for me for a while.

I have drawn an example of my problem. In this example you will see that there are 6 textboxes (3 in each row). When the user is typing in the data and she only needs the first row I need to have her skip the remaining three text boxes and goto the total materials textbox. This cannot be done through tabbing over though because there are actually 8 rows in the actual userform. how can I go about this?

quantity | parts | amount
=======|=====|=============
txtb1 | txtb2 | txtb3
======|=======|=============
txtb4 |txtb5 | txtb6
=======|======|=============

sure but wouldn’t I have to do that for every row in the spreadsheet?

I tried this code below too but it still puts in tru or false

Code:

Private Sub cmdenter_click()
If checkbox1 Then
   Cells(Rows.Count,"BB").End(xlUp).Offset(1).Value = "Yes"
Else
   Cells(Rows.Count,"BB").End(xlUp).Offset(1).Value = "No"
End If

End Sub

any other ideas?

the data entry operator is fast so she would like to just skip the empty text boxes (thats what the quantity parts and amount are.
so if there is quantity = 1 part = tire amount = 5 cents and nothinjg else she can skip the other text boxes and continue to the total materials text box.

yes sir, I would like for it to show the first match then tell me how many are within that certain spreadsheet. My spreadsheets are named after the date MMMYYYY (Jul2010) etc…

P.s.

I’m sorry I did not respond any earlier but I was hospitalized. Thank you once again for any and all assistance.

I’m also having problems with my data not getting to my spreadsheet. I have attached a copy of what I have done so far. Please keep in mind I’m doing this from my school books. If you or anybody else can think of a better way to get this done please inform. I will take any and all assistance. But it has to be in visual basic and excel.

Thank you.

ok one last word on this subject.

Is there any way That I can make it go faster because the wait is at least 4 minutes if there is more than 10 child/parents it has to find.?

And now that I have completed it how would you have written the code? Just curious!!

thank you

ok, I finally have it working where it finds everything needed except. If the cell conatins PSA-100 it finds everything that mayches it exactly. What about wildcards. How can I not only find the PSA-100’s but also the PSA-100-6 & PSA-100-msa & PSA-100klu Etc… after this I should be done.

Current code below:

rivate lngMaxRow As Long

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Private Sub EXEC_BTN_click()
Application.ScreenUpdating = False

Range(“H6:H65536”).EntireRow.Hidden = False
lngMaxRow = Range(“I65536”).End(xlUp).Row

If IsEmpty(ActiveCell) = False Then
‘ActiveCell has something in it.
If ActiveCell.Column = 8 Or ActiveCell.Column = 9 Then
‘ActiveCell is in Column H or I.
Range(“H6:I” & lngMaxRow).EntireRow.Hidden = True
End If
End If

HiliteMeAndSiblings ActiveCell

Application.ScreenUpdating = True

End Sub

Sub HiliteMeAndSiblings(oCell As Range)

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell & “*” Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
HiliteMeAndSiblings2 ActiveCell

End Sub

Sub HiliteMeAndSiblings2(oCell As Range)

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, 1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“I” & i) = oCell Then
Range(“H” & i).EntireRow.Hidden = False
End If
Next i

End Sub

Point well taken!!! My apolgies, I’m just frustrated thats all.

I told you guys this was tough. It seems that you all gave up on me.

Also I have reviewed the Intersect on vba help. dont really understand how that would help. what can I say I’m an Idiot.

Returns a Range object that represents the rectangular intersection of two or more ranges.

expression.Intersect(Arg1, Arg2, …)

expression Optional. An expression that returns an Application object.

Arg1, Arg2, … Required Range. The intersecting ranges. At least two Range objects must be specified.

Example
This example selects the intersection of two named ranges, rg1 and rg2, on Sheet1. If the ranges don’t intersect, the example displays a message.

Worksheets(“Sheet1”).Activate
Set isect = Application.Intersect(Range(“rg1”), Range(“rg2”))
If isect Is Nothing Then
MsgBox “Ranges do not intersect”
Else
isect.Select
End If

My new code is below:

Private lngMaxRow As Long

Private Sub EXEC_BTN_click()
Application.ScreenUpdating = False
Range(“H4:H65536”).EntireRow.Hidden = False
lngMaxRow = Range(“I65536”).End(xlUp).Row

If ActiveCell “” Then
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

HiliteMeAndSiblings ActiveCell
End If
Application.ScreenUpdating = True

End Sub

Sub HiliteMeAndSiblings(oCell As Range)

Dim i As Long

oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False And Range(“H” & i).EntireRow.Hidden = False
End If
Next i

End Sub

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

thank you gentlemen.

I told you I was a newbie….. I really suck at this thats why I’m going to school for it to hopefully get better. I really do appreciate you patience as well as your knowledge. thank you.

1). I only have 831 rows of data, I thought it would make it go faster if it did not read all the blank cells.

2). the “lorIndex = xlColorIndexAutomatic” was just a left over line from what Hans gave me I have since taken it out.

3). I have put the Hide all rows within the click event. along with the if statement.

4). I have put the screen updating within the click event as well.

5). The debug print line has been taken out. As I stated earlier I was reading from the book.

6). Hans’ code allowed me to hilite anything within columns H and I (parent and child) It looked through the whole column and if it found it it would hilight it.
Yes I agree there are some parts of this coding that is way over my head. That is the reason I ask these stupid questions. My teacher keeps telling us that in the real world people search out the answers rather through the Internet or books. I have tried to do both. It just gets so confusing day after day trying to understand what he wants out of us and what the book states to do and what the people on line tell me what to do. I really do appreciate both you and Hans for you superior Knowledge (and no I’m not a** kissing) Its true. I have stated before in the past that I have tried other sites but they are filled with Newbies like myself who really do not know what the hell they are talking about. Thats why I kep coming here. I bought the excel 2000 book and it gave me this site for reference. If I have become a Nuisance please say so and I will stop. thank you for your time and all of your efforts.

Sorry Steve, I was trying some things out of the book.

I have repaired it. This is what I have so Far.

Private Sub CommandButton1_Click()
With Sheet1
ActiveWorkbook.RefreshAll
End With
End Sub

Private Sub EXEC_BTN_click()
Range(“H4:H1000”).EntireRow.Hidden = False
lngMaxRow = Range(“I1000”).End(xlUp).Row
lorIndex = xlColorIndexAutomatic
HiliteMeAndSiblings ActiveCell
End Sub

Sub HiliteMeAndSiblings(oCell As Range)
Application.ScreenUpdating = False
Range(“H4:I” & lngMaxRow).EntireRow.Hidden = True

Dim i As Long
‘oCell.Offset(0, -1).Resize(1, 2).Font.ColorIndex = 5
oCell.Offset(0, -1).EntireRow.Hidden = False
For i = 2 To lngMaxRow
Debug.Print i
If Range(“H” & i) = oCell Then
Range(“I” & i).EntireRow.Hidden = False
End If
Next i
Application.ScreenUpdating = True
End Sub

Tell me Steve what would your code look like. I really do not want to fail this. But I’m just so confused between the VB books the Excel Books and what people are telling me how it should look and work. AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH!!!!! Real Life Programming can’t be this way, can it?

i’m sorry to say this but i must leave for school now see you all later I will as always check for any updates. see you all tommorrow my friends