Excel Userform Variable not set

[denExc]

Hello,

I got a Userform which allows to enter Data in Textboxes and load the belonging values in the remaining Textboxes. My Comboboxes are linked, this means when i select a country then the belonging Cities should appear in Comobox City. My Code is working when i remove the option explicit. But now i declared the Variables, the only thing i cant do, is to set a Variable. Excel tells me "Object Variable or With block variable not set, Runtime error 91". Can anyone please help me solve this problem?

Option Explicit
DIM Sheetn As Worksheet
Dim lastcol As long
Dim n As Integer
Dim j As Integer

Sub Userform_Initialize()

sheetn = "Sheet1"
lastcol = Sheets(Sheetn).Cells(1, 10000).End(xlToLeft).Column
For n = 1 To lastcol
Combobox1 = Sheets(Sheetn).Cells(1, n).Value
Me.Controls("Combobox1").AddItem Combobox1

next n

End Sub

There is a second part of Code, its under a Sub Combobox1_Click()

But when i switch it off the error still appears, so i guess its the Userform_Initialize()

regards

 

[NARAYANK991]

Hi ,

Since no workbook with data and code has been uploaded , I can also just guess.

The following statement is a problem :

sheetn = "Sheet1"

since this expects sheetn to be declared as a String or Variant type variable , whereas your declaration is as a Worksheet type variable. In fact since your other statements also refer to sheetn as a String variable , it is only your declaration which is wrong , and not the statements themselves. Change your declaration to :

Dim sheetn as String

The following statements may be the problem :

Combobox1 = Sheets(Sheetn).Cells(1, n).Value
Me.Controls("Combobox1").AddItem Combobox1

See how to add items to a combobox in the following link :

http://www.contextures.com/Excel-VBA-ComboBox-Lists.html

Narayan

 

[denExc]

Thank you, i will explain it later, this evening.

 

[denExc]

Thank you very much, i declared it as String and now its working. The second codeline should be also a problem? I dont get an error message, later i will give you an example. I need another tip.