Check value to add options to listbox

Yukster

New Member
Joined
Dec 15, 2016
Messages
3
Hi All,

I'm quite new to VBA myself and a bit stuck on the following code.

The code itself is supposed to check a name group on sheet 7. If this contains the value "3rd" this should add the group "third" to the listbox on the userform. I've currently got this on the code of the userform itself but I'm getting the error: "Object variable or with block variable not set"

The code interfering is the bold code below.

Code:
Private Sub Issueform_Click()


Dim Fault As String
Dim Service As String
[B]Dim departmentlist As Object[/B]


Fault = "False"
Service = "False"


If Sheet7.Range("fors").Value = "Fault" Then Fault = "True"
If Sheet7.Range("fors").Value = "Service Request" Then Service = "True"


[B]If Sheet7.Range("floor").Value = "3rd" Then departmentlist.RowSource = Sheet7.Range("third").Text[/B]




    With Infoform
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .namebox.Text = Sheet7.Range("Name").Value
        .windowsusernamebox.Text = Sheet7.Range("username").Value
        .systemdropdown.Text = Sheet7.Range("system").Value
        .faultoption.Value = Fault
        .serviceoption.Value = Service
        .Affectednumber.Text = Sheet7.Range("people")
        .ST1.Text = Sheet7.Range("servicetag1")
        .ST2.Text = Sheet7.Range("servicetag2")
        .ST3.Text = Sheet7.Range("servicetag3")
        .issue.Text = Sheet7.Range("description")
        .Show
        
        End With


End Sub


Any advice is much appreciated!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try...

Code:
[B]If Sheet7.Range("floor").Value = "3rd" Then departmentlist.RowSource = Sheet7.Range("third")[COLOR=#ff0000].Address(External:=true)[/COLOR][/B]

Hope this helps!
 
Upvote 0
Try...

Code:
[B]If Sheet7.Range("floor").Value = "3rd" Then departmentlist.RowSource = Sheet7.Range("third")[COLOR=#ff0000].Address(External:=true)[/COLOR][/B]

Hope this helps!

Unfortunately still getting the same error message :(
 
Upvote 0
Actually, now that I've taken another look, you'll get that error if departmentlist isn't the code name for your listbox. In your code, you've declare it as an object, but you haven't assigned it your listbox. In any case, I would simply use the code name (the name listed in the Properties window) to refer to your listbox.
 
Last edited:
Upvote 0
So what form is it on? Infoform? If so, maybe something like this?
Code:
    With Infoform
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        [B]If Sheet7.Range("floor").Value = "3rd" Then .departmentlist.RowSource = Sheet7.Range("third").Text[/B]
        .namebox.Text = Sheet7.Range("Name").Value
        .windowsusernamebox.Text = Sheet7.Range("username").Value
        .systemdropdown.Text = Sheet7.Range("system").Value
        ...
Not exactly sure what you're trying to do as far as the RowSource (adding something to a listbox would generally not include changing the RowSource), so maybe a combination of this and what's above?
 
Upvote 0
Not exactly sure what you're trying to do as far as the RowSource (adding something to a listbox would generally not include changing the RowSource), so maybe a combination of this and what's above?

This unfortunately didn't work either and I'm getting the error message: Invalid use of null.

Infoform is a userform on which people can fill in the issue that they're having. The idea is that people can select a button on which floor they are and based on that all the departments on that floor will pop up in the listbox on the userform. I'm currently trying to create a trigger that works as following:

1. User selects the floor they are on by clicking the button on the user form -> This will trigger "floor" and cause it to have the number of the floor in it.
2. User will be redirected to a seperate sheet with a map on it where they select where they're sitting -> Nothing special here.
3. User will click on a return button next to the map which will reopen the userform and will populate the listbox with the departments that are on that floor (which is the "third" the rowsource is refering to)-> Currently I'm trying to get the rowsource changed based on the data in "floor" but Rowsource is not the way to go?

Sorry I'm quite new to this so if there are any better ways of doing this please feel free to give any feedback.
 
Upvote 0
Hard to determine, but since it is a new error...I'd check on the rowsource part specifically (is that where your error is occurring)?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top