Member Avatar for sandeep.shivara

i have made a userform which has two textbox and one button. when we enter a number in textbox1 and click button then this number will be searched in excel sheet and the value to corresponding cell should be displayed in textbox2.
please help

  • 2 Contributors
  • 1 Reply
  • 93 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by hkdani
Member Avatar for hkdani

Working with Excel Sheets is not too well documented anywhere.
So, here is some code to open up an existing excel worksheet.
But you have to know the worksheet name. And you have to be aware
of what the Actual Row and Column is.
If you know you have values in A1 and A2, then you could input
those values into txtCellValue.Text, the value in that range (if valid)
should appear in txtResult.

Option Explicit
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlBook2 As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Private Sub cmdDisplay_Click()
    txtResult = xlSheet.Range(txtCellValue.Text).Value
End Sub

Private Sub Form_Load()
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open("f:\temp.xls")
    Set xlSheet = xlBook.Worksheets("MySheet")
End Sub

Private Sub Form_Unload(Cancel As Integer)
    xlBook.Close
    xlApp.Quit
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing
End Sub

Hope this gets you started.

Regards,

Hank

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.