VB-6 / Excel interaction
-
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 to move the picture of the parts to be selected. Private Sub scr_Change() On Error GoTo erh p4l80e.Top = scr.Value ' p24L80E.Top = sc