Object required error

[Quintin8709]

MREXCEL

Can you please help on this. I've got a combobox in a userform. I'm trying to assign a variable called shtRB to different worksheets depending on the cb selected value.

Private Sub CommandButton1_Click()


Dim shtrb As Worksheet
Dim namecell As String
Dim datecell As Date


If ufRB.cbtype.Value = "weekly" Then
Set shtrb = Worksheets("1")
Else
Set shtrb = Worksheets("2")
End If


'cells names gives error object required
Set namecell = shtrb.Range("D11")
Set datecell = shtrb.Range("j11")


namecell.Value = ufRB.tbname.Value
datecell.Value = ufRB.tbdate.Value




End Sub

 

[AlphaFrog]

Declare the cell variables as range objects.

[COLOR=#333333]Private Sub CommandButton1_Click()[/COLOR]


[COLOR=#333333]Dim shtrb As Worksheet[/COLOR]
[COLOR=#333333]Dim namecell As [/COLOR][COLOR=#ff0000]Range[/COLOR]
[COLOR=#333333]Dim datecell As [/COLOR][COLOR=#ff0000]Range[/COLOR]


[COLOR=#333333]If ufRB.cbtype.Value = "weekly" Then[/COLOR]
[COLOR=#333333]Set shtrb = Worksheets("1")[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Set shtrb = Worksheets("2")[/COLOR]
[COLOR=#333333]End If[/COLOR]


[COLOR=#dda0dd]'cells names gives error object required[/COLOR]
[COLOR=#333333]Set namecell = shtrb.Range("D11")[/COLOR]
[COLOR=#333333]Set datecell = shtrb.Range("j11")[/COLOR]


[COLOR=#333333]namecell.Value = ufRB.tbname.Value[/COLOR]
[COLOR=#333333]datecell.Value = ufRB.tbdate.Value[/COLOR]




[COLOR=#333333]End Sub[/COLOR]

Alternatively, You don't need variables...

[COLOR=#333333][FONT=Verdana]Private Sub CommandButton1_Click()
[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]If ufRB.cbtype.Value = "weekly" Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Worksheets("1")
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]    [/FONT][/COLOR][COLOR=#333333][FONT=Verdana].Range("D11").Value = [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]ufRB.tbname.Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]    [/FONT][/COLOR][COLOR=#333333][FONT=Verdana].Range("J11").Value = [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]ufRB.tbdate.Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]
[/FONT][/COLOR]End With
[COLOR=#333333][FONT=Verdana]Else[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Worksheets("2")
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]    [/FONT][/COLOR][COLOR=#333333][FONT=Verdana].Range("D11").Value = [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]ufRB.tbname.Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]
[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]    [/FONT][/COLOR][COLOR=#333333][FONT=Verdana].Range("J11").Value = [/FONT][/COLOR][COLOR=#333333][FONT=Verdana]ufRB.tbdate.Value[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]
[/FONT][/COLOR]End With
[COLOR=#333333][FONT=Verdana]End If[/FONT][/COLOR]

[COLOR=#333333][FONT=Verdana]End Sub[/FONT][/COLOR]