Load userform with information from an existing worksheet

mari_hitz · #1 (**permalink**) 06-08-11, 16:11

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!

Colin Legg · #2 (**permalink**) 06-11-11, 11:17

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

Quote:

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.

mari_hitz · #3 (**permalink**) 06-13-11, 07:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech