Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Load userform with information from an existing worksheet

    Hi guys,

    I am new at the forum and I am new with VBA in Excel. I want that a userform to collect information from an existing worksheet.
    I have copied the code from another tool that I found. To view the different information there is a comboBox will display the list of the information in the worksheet.
    After you choose the correct name the userform should be loaded or updated with the information that is in the worksheet.
    The code seems to work and the ComboBox loads the list, however when I choose a name from the list the code stops and returns me an error with the Offset.
    I do not know what offset means, could you please explain? I also do not know what the expresion ("B" & r -1) means. Do you have any suggestions?

    This is the code that I have:
    Code:
    Private Sub ComboBox2_Change()
    Dim r As Integer
    r = 2
    'Load form fields
    If OptionIssue = True Then
    Do Until Sheet3.Range("B" & r - 1) = ComboBox2.Text
    If Sheet3.Range("B" & r) = ComboBox2.Text Then
    TextBox 1 = Sheet3.Range("B" & r).Offset(1, 0)
    TextBox 6 = Sheet3.Range("B" & r).Offset(1, 0)
    TextBox 2 = Sheet3.Range("B" & r).Offset(2, 0)
    r = r + 1
    Else
    r = r + 1
    End Sub
    
    End Sub
    
    Private Sub CommandButton2_Click()
    Unload UserForm3
    End Sub
    Private Sub CommandButton3_Click()
    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
    .To = "xxx@hotmail.com"
    .Subject = "Your case reply"
    .Display
    
    End With
    Set olMail = Nothing
    Set olApp = Nothing
    
    End Sub
    Private Sub UserForm_Acivate()
    OptionIssue = True
        ComboBox2.Clear
        Dim row As Integer
        row = 2
        Do Until Sheet3.Range("A" & row + 4) = ""
           ComboBox2.AddItem Sheet3.Range("B" & row + 2)
            row = row + 5
        Loop
    TextBox1 = ""
    TextBox2 = ""
    TextBox6 = ""
    ComboBox2 = ""
    
    End Sub
    
    Private Sub OptionIssue_Click()
    TextBox1 = ""
    TextBox6 = ""
    TextBox2 = ""
    
    'Load list of templates
    'Starts Search engine
    ComboBox2.Clear
    Dim row As Integer
    row = 2
    Do Until Sheet3.Range("A" & row + 4) = ""
    ComboBox2.AddItem Sheet3.Range("B" & row + 2)
    row = row + 5
    
    Loop
    
    End Sub

    Thanks!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    In VBA, & is an operator which concatenates (joins) two strings together into one string. So
    Code:
    "Hello " & "The World"
    would be the same as
    Code:
    "Hello The World"
    In your code:
    Code:
    r = 2
     
    'Load form fields
     
    If OptionIssue = True Then
        Do Until Sheet3.Range("B" & r - 1) = ComboBox2.Text
    Since r equals to on the first iteration of the loop, this expression:
    Code:
    Do Until Sheet3.Range("B" & r - 1) = ComboBox2.Text
    Becomes:
    Code:
    Do Until Sheet3.Range("B" & 2 - 1) = ComboBox2.Text
    2-1 is obviously 1, so it then becomes:
    Code:
    Do Until Sheet3.Range("B1") = ComboBox2.Text
    I do not know what offset means, could you please explain?
    You can always check for explanations and meanings in the VBA helpfile (open the Visual Basic Editor and then press F1). If you look under the 'Range.Offset Property' help topic you can see that:
    Quote Originally Posted by helpfile
    Excel Developer ReferenceRange.Offset PropertyReturns a Range object that represents a range that’s offset from the specified range.

    Syntax
    expression.Offset(RowOffset, ColumnOffset)
    It gives further details about RowOffset and ColumnOffset and then some examples.


    So:
    • Range("A1").Offset(1,3) would return a reference to D2 because D2 is one row down and three columns to the right of A1.
    • Range("A1:B1").Offset(1,3) would return a reference to D2:E2.
    • Range("A1").Offset(-1,-1) would cause a runtime error to be raised because you would be trying to get a reference to a range which is off the top and side of the worksheet.

    In this case, I think you get an error because the Do... Until Loop is not very well constructed. The code you posted cannot be exactly what you have in your file (part of the code is missing - for example, the 'Next' statement to close your loop block), but your loop basically translates to:

    "Keep looping down the column until you find a cell with ComboBox2.Text in it, then do something."

    The problem is what if it can't find Combobox2.Text? It will keep going down to the column until it runs off the bottom of your worksheet and then you'll get an error. You need to think about how you should cater for this eventuality by adding some sort of condition so that your loop can gracefully stop.

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Hi Colin,

    Thanks for the explanation, it has been very helpful to me. I was able to somehow ( I do not know if right or wrong) to change the code and now it seems to work. However I am still not happy with the results.
    It turns out that the information that I load in the first userform copies the data and it is sent to a sheet (sheet 3), has you can see in my picture:

    The code that I have to upload information to this sheet is:
    Code:
    Private Sub OkButton_Click()
    If TextBox1.Text = "" Or TextBox2.Text = "" Or _
    TextBox3.Text = "" Or ComboBox2.Value = "" Or TextBox6.Text = "" Then
    MsgBox "Please complete all the required fields"
    Exit Sub
    Else
    End If
    TextBox2.SetFocus
    Sheet3.Activate
    If MsgBox("Save the request?", vbYesNo, "Warning") = vbNo Then
    Call DeActivateSheet.Sheets("Sheet3")
    Exit Sub
    Else
    End If
    Range("A1").Select
    Do While Not ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
    Loop
    Dim row As Integer
    row = 2
    If Sheets("Sheet3").Range("A2") = "" Then
    ActIndex = "0"
    Else
    Do Until Sheets("Sheet3").Range("A" & row + 5) = ""
    ActIndex = Sheets("Sheet3").Range("A" & row + 5)
    row = row + 5
    Loop
    End If
    ActiveCell.Value = Val(ActIndex) + Val(1)
    ActIndex = ActiveCell.Value
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = "Name of the request"
    ActiveCell.Offset(0, 1).Value = TextBox6.Text
    ActiveCell.Offset(1, 0).Value = "Date Issue"
    ActiveCell.Offset(1, 1).Value = TextBox1.Text
    ActiveCell.Offset(2, 0).Value = "Issue Description"
    ActiveCell.Offset(2, 1).Value = TextBox2.Text
    ActiveCell.Offset(3, 0).Value = "Impact if not fixed"
    ActiveCell.Offset(3, 1).Value = TextBox3.Text
    ActiveCell.Offset(4, 0).Value = "Submited By"
    ActiveCell.Offset(4, 1).Value = TextBox5.Text
    ActiveCell.Offset(5, 0).Value = "Gu affected"
    ActiveCell.Offset(5, 1).Value = ComboBox4
    ActiveCell.Offset(6, 0).Value = "Priority"
    ActiveCell.Offset(6, 1).Value = ComboBox2
    End
    Cells.EntireRow.AutoFit
    Cells.EntireColumn.AutoFit
    If ComboBox4 = True Then
    TextBox1.Text = ""
    TextBox1.Enabled = False
    TextBox1.Locked = True
    TextBox2.Text = ""
    TextBox2.Enabled = False
    TextBox2.Locked = True
    TextBox3.Text = ""
    TextBox3.Text = ""
    ComboBox3 = ""
    UserForm1.ComboBox2 = False
    End If
    Sheets("Inicio").Select
    Sheets("Sheet3").Visible = False
    Me.Hide
    MsgBox "Success!!!"
    Call DeActivateSheet.Sheets("Sheet3")
    End Sub
    And then I try load the information to the userform with the code I had before that I have customized. The userform loades the information but some information is being misplaced. For example the name of the issue it is being brought in the description field and in the combobox where it should appear the list with the names of the issues loaded it appears the description.
    I have read the information you gave me about the offset and I believe this is the reason why this is happening but I still do not understand why.
    Here it is the code that I have changed with the information you have provided to me:

    Code:
    Private Sub ComboBox2_Change()
    Dim r As Integer
    r = 2
    'Load form fields
    If OptionIssue = True Then
    Do Until Sheet3.Range("B" & r - 1) = ComboBox2.Text
    If Sheet3.Range("B" & r) = ComboBox2.Text Then
    TextBox1 = Sheet3.Range("B" & r).Offset(-1, 0)
    TextBox6 = Sheet3.Range("B" & r).Offset(3, 0)
    TextBox2 = Sheet3.Range("B" & r).Offset(1, 0)
    TextBox3 = Sheet3.Range("B" & r).Offset(-2, 0)
    TextBox4 = Sheet3.Range("B" & r).Offset(2, 0)
    TextBox7 = Sheet3.Range("B" & r).Offset(4, 0)
    
    
    r = r + 1
    Else
    r = r + 1
    End If
    
    Loop
    End If
    
    End Sub
    Private Sub CommandButton2_Click()
    Unload UserForm3
    End Sub
    Private Sub CommandButton3_Click()
    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
    .To = "xxx@hotmail.com"
    .Subject = "Your case reply"
    .Display
    
    End With
    Set olMail = Nothing
    Set olApp = Nothing
    
    End Sub
    Private Sub UserForm_Acivate()
    OptionIssue = True
        ComboBox2.Clear
        Dim row As Integer
        row = 2
        Do Until Sheet3.Range("A" & row + 4) = ""
           ComboBox2.AddItem Sheet3.Range("B" & row + 2)
            row = row + 5
        Loop
    TextBox1 = ""
    TextBox6 = ""
    TextBox2 = ""
    TextBox3 = ""
    TextBox4 = ""
    TextBox7 = ""
    
    
    End Sub
    
    Private Sub OptionIssue_Click()
    TextBox1 = ""
    TextBox6 = ""
    TextBox2 = ""
    TextBox3 = ""
    TextBox4 = ""
    TextBox7 = ""
    
    
    'Load list of templates
    'Starts Search engine
    ComboBox2.Clear
    Dim row As Integer
    row = 2
    Do Until Sheet3.Range("A" & row + 4) = ""
    ComboBox2.AddItem Sheet3.Range("B" & row + 2)
    row = row + 5
    
    Loop
    
    End Sub
    Thanks and I am sorry for my ignorance in this case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •