VB-6 Project
-
Hi all, I am new to this forum so please bear with me until I learn how things are done here. I am trying to write a “VB-6” program where the user can click on a picture of a part and the program then opens an “Excel” price list for them. I used a transparent text box over the picture. You can start the program, click the picture, and an “Excel” window opens with the proper line highlighted. Then, if you close the “Excel” window and try to click on the picture again, the program balks. Part of the “Excel” window appears but it is empty. The program doesn’t crash. It just refuses to show the “Excel” sheet again. This is the code that I used: Dim AppExcel As Excel.Application This sub sets the focus on the picture so the scroll bar doesn’t blink. Private Sub Form_Activate() p4l80e.SetFocus End Sub This sub tells the program to open “Excel”. Private Sub Form_Load() Set AppExcel = CreateObject("Excel.Application") Workbooks.Open FileName:="D:\K and D (152598).xls" End Sub This sub makes “Excel” visible, finds the proper row, and highlights the entire row. Private Sub lbl034_Click() 'On Error GoTo erh AppExcel.Visible = True Cells.Find(What:="34034b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate 'Highlights the entire row. If IsEmpty(ActiveCell) Then Exit Sub On Error Resume Next If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft) If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight) Range(LeftCell, RightCell).Select Exit Sub erh: MsgBox Error(Err) End Sub This is a different attempt at the above code with a different part number. It will work the first time around also. Second attempt, it also will not respond. Private Sub lbl070_Click() On Error GoTo erh 'Set AppExcel = CreateObject("Excel.Application") Workbooks.Open FileName:="D:\K and D (152598).xls" Cells.Find(What:="34070e", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate AppExcel.Visible = True Exit Sub erh: MsgBox Error(Err) End Sub The following code works fine. It just controls the scroll bar
-
Hi all, I am new to this forum so please bear with me until I learn how things are done here. I am trying to write a “VB-6” program where the user can click on a picture of a part and the program then opens an “Excel” price list for them. I used a transparent text box over the picture. You can start the program, click the picture, and an “Excel” window opens with the proper line highlighted. Then, if you close the “Excel” window and try to click on the picture again, the program balks. Part of the “Excel” window appears but it is empty. The program doesn’t crash. It just refuses to show the “Excel” sheet again. This is the code that I used: Dim AppExcel As Excel.Application This sub sets the focus on the picture so the scroll bar doesn’t blink. Private Sub Form_Activate() p4l80e.SetFocus End Sub This sub tells the program to open “Excel”. Private Sub Form_Load() Set AppExcel = CreateObject("Excel.Application") Workbooks.Open FileName:="D:\K and D (152598).xls" End Sub This sub makes “Excel” visible, finds the proper row, and highlights the entire row. Private Sub lbl034_Click() 'On Error GoTo erh AppExcel.Visible = True Cells.Find(What:="34034b", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate 'Highlights the entire row. If IsEmpty(ActiveCell) Then Exit Sub On Error Resume Next If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft) If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight) Range(LeftCell, RightCell).Select Exit Sub erh: MsgBox Error(Err) End Sub This is a different attempt at the above code with a different part number. It will work the first time around also. Second attempt, it also will not respond. Private Sub lbl070_Click() On Error GoTo erh 'Set AppExcel = CreateObject("Excel.Application") Workbooks.Open FileName:="D:\K and D (152598).xls" Cells.Find(What:="34070e", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate AppExcel.Visible = True Exit Sub erh: MsgBox Error(Err) End Sub The following code works fine. It just controls the scroll bar
-
hi, just close the workbook and also quit the excel application before exiting the Sub routine. :laugh: Paritosh
Hi Paritosh, I thought that this was the problem but I haven't figured out how to do it. If I use a "close" event in the sub that shows the "Excel" workbook, the workbook flashes on the screen and back off before you get to look at it. I tried to put it in my main form "activate" sub and that didn't work either. I need this to happen automaticly as the user closes the "Excel" window so they can just click on another part of the picture. Could you give me an idea of what the code would look like and what sub to put it in? Like I said, I'm "self-taught" in "VB" so my teacher isn't the sharpest tool in the shed. Thanks again, Garner