Userform cannot be run from different sheet

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have this userform code that cannot be executed from other sheet, so it can only be executed from sheet 6 (Activity Data). When i tried to execute it from other sheet, it shows runtime error 1004: application-defined or object-defined error. Any idea how to solve this problem?

VBA Code:
Private Sub ComboBox5_Click()
Dim t As Double
With ComboBox5
    t = Val(.List(.ListIndex))
    .value = Format(t, "hh:mm")
End With
End Sub

Private Sub ComboBox8_Click()
Dim t As Double
With ComboBox8
    t = Val(.List(.ListIndex))
    .value = Format(t, "hh:mm")
End With
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
Dim activity_id As String
activity_id = ComboBox9.value
Lastrow = Sheets("Activity Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Activity Data").Cells(i, 7).value = activity_id Then
Sheets("Activity Data").Cells(i, 8).value = ComboBox1.value
Sheets("Activity Data").Cells(i, 9).value = TextBox1.Text
Sheets("Activity Data").Cells(i, 11).value = ComboBox2.value
Sheets("Activity Data").Cells(i, 12).value = ComboBox3.value
Sheets("Activity Data").Cells(i, 13).value = ComboBox4.value
Sheets("Activity Data").Cells(i, 14).value = ComboBox5.value
Sheets("Activity Data").Cells(i, 15).value = ComboBox8.value
Sheets("Activity Data").Cells(i, 10).value = TextBox2.Text
End If
Next
End Sub

Private Sub CommandButton4_Click()
Dim activity_id As String
activity_id = ComboBox9.value
Lastrow = Sheets("Activity Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lastrow
If Sheets("Activity Data").Cells(i, 7).value = activity_id Then
ComboBox1.value = Sheets("Activity Data").Cells(i, 8).value
TextBox1.Text = Sheets("Activity Data").Cells(i, 9).value
ComboBox2.value = Sheets("Activity Data").Cells(i, 11).value
ComboBox3.value = Sheets("Activity Data").Cells(i, 12).value
ComboBox4.value = Sheets("Activity Data").Cells(i, 13).value
ComboBox5.value = Sheets("Activity Data").Cells(i, 14).value
ComboBox8.value = Sheets("Activity Data").Cells(i, 15).value
TextBox2.Text = Sheets("Activity Data").Cells(i, 10).value
End If
Next
End Sub

Private Sub UserForm_Initialize()
 ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Range("G65536").End(xlUp)).Address
 End Sub

Any idea how to solve this problem?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,461
Office Version
  1. 365
Platform
  1. Windows
As you're using rowsource, then you need to specify the sheet like
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Address(, , , 1)
Although I would recommend
VBA Code:
ComboBox9.List= Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Value
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
As you're using rowsource, then you need to specify the sheet like
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Address(, , , 1)
Although I would recommend
VBA Code:
ComboBox9.List= Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Value
Im sorry but both of them give me error. The first one gives me run time error 438: object doesnt support this property or method. Meanwhile the second one gives me error: type mismatch
 

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
As you're using rowsource, then you need to specify the sheet like
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Address(, , , 1)
Although I would recommend
VBA Code:
ComboBox9.List= Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Value
Sorry, i did a typo and the first one works fine. Thank you very much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,461
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,407
Messages
5,624,587
Members
416,036
Latest member
eloisa manzanarez

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
Top