get data from any sheet with VBA

[kelly mort]

I have this code that I am using to send data to any sheet in my workbook.

Private Sub CmdSend_Click()
    Dim cNum As Integer
    Dim x As Integer
    Dim nextrow As Range
    Dim sht As String
    sht = ComboBox1.Value
        If Me.ComboBox1.Value = "" Then
            MsgBox "Please select a sheet from the combobox"
            Exit Sub
        End If
        cNum = 13
        Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        For x = 1 To cNum
            nextrow = Me.Controls("Reg" & x).Value
            Set nextrow = nextrow.Offset(0, 1)
        Next
        For x = 1 To cNum
            Me.Controls("Reg" & x).Value = ""
        Next
            
End Sub

How do I modify the one below here to get the data from the sheet for me?

Sub Lookup()
        Dim rngFind As Range
        Dim strFirstFind As String
        
        On Error GoTo errHandler:
        lstWin.ColumnCount = 11
        Dim myArray As Variant
        myArray = [C2].Resize(, lstWin.ColumnCount + 1).Value
        lstWin.List = myArray
        lstWin.Clear
        With Sheet1.Range("C3:C303")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        
        Do
        If rngFind.Row > 1 Then
        lstWin.AddItem rngFind.Offset(0, -1)
        lstWin.List(lstWin.ListCount - 1, 1) = rngFind
        lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, 1)
        lstWin.List(lstWin.ListCount - 1, 3) = rngFind.Offset(0, 2)
        lstWin.List(lstWin.ListCount - 1, 4) = rngFind.Offset(0, 3)
        lstWin.List(lstWin.ListCount - 1, 5) = rngFind.Offset(0, 4)
        lstWin.List(lstWin.ListCount - 1, 6) = rngFind.Offset(0, 5)
        lstWin.List(lstWin.ListCount - 1, 7) = rngFind.Offset(0, 6)
        lstWin.List(lstWin.ListCount - 1, 8) = rngFind.Offset(0, 7)
        lstWin.List(lstWin.ListCount - 1, 9) = rngFind.Offset(0, 8)
        lstWin.List(lstWin.ListCount - 1, 10) = rngFind.Offset(0, 9)
        End If
        Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
        End With
        Me.Reg1.Enabled = False
        On Error GoTo 0
        Exit Sub
        
errHandler::
            MsgBox "Check your entry for errors"
    End Sub

My headache just got tough. I hope to get your attention

 

[kelly mort]

Thank you very much for the time and effort

 

[Computerman]

kelly mort,
The code in your first codebox should have done that:
Private Sub CmdSend_Click()
Dim cNum As Integer
Dim x As Integer
Dim nextrow As Range
Dim sht As String
sht = ComboBox1.Value

Oh wait I think I see why it didn't. The CmdSend_Click()sub is set as a private sub thus not allowing the other sub to use any variables from tCmdSend_Click() sub. But glad you got it to work.

Computerman

 

[kelly mort]

kelly mort,
The code in your first codebox should have done that:
Private Sub CmdSend_Click()
Dim cNum As Integer
Dim x As Integer
Dim nextrow As Range
Dim sht As String
sht = ComboBox1.Value

Oh wait I think I see why it didn't. The CmdSend_Click()sub is set as a private sub thus not allowing the other sub to use any variables from tCmdSend_Click() sub. But glad you got it to work.

Computerman

Okay i am now learning so sometimes i find it tough to read the codes and understand them. But one day i will be cool.

 

[Computerman]

Okay i am now learning so sometimes i find it tough to read the codes and understand them. But one day i will be cool.

"But one day I will be cool" - of that I have no doubt. Kelly mort, never stop asking questions, never stop learning. Everyone here started with a first step, just some of us have had more time to take further steps. You will get there as long as you continue to wonder


Computerman

 

[kelly mort]

Thank you computerman

 

[kelly mort]

I am wondering if i can let the value in a combobox appear on a sheet when the combobox is updated.

 

[Computerman]

kelly mort,
certainly. create a test form and place a ComboBox on it ( but a way from cell A1). Name the ComboBox ComboBox1. Put the code below in the ComboBox1 change event:

Private Sub ComboBox1_Change()
Range("a1").Value = ComboBox1.Value
End Sub

Now every time the value in the combo box changes the value in Cell A1 should change to match it

Computerman

 

[kelly mort]

So from my userform can it be like;

Sheet1.Range("a1").Value = ComboBox1.Value

Since i want it on sheet1?

 

[Computerman]

Kelly mort,
That is correct. And if you want the combobox value in a different cell on sheet 1 then change the "A1" to what ever Column and Row you want the value to show up in, don't forget to put the double quotes around it.

Computerman

 

[kelly mort]

Kelly mort,
That is correct. And if you want the combobox value in a different cell on sheet 1 then change the "A1" to what ever Column and Row you want the value to show up in, don't forget to put the double quotes around it.

Computerman

Copied that