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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Just change the sheet name, to the sheet you want it to work on.
 
Upvote 0
I'm sorry but that doesn't make any sense to me. What exactly are you trying to do?
 
Upvote 0
I'm sorry but that doesn't make any sense to me. What exactly are you trying to do?
I have to put the command button on home page and the data generated from userform should not be included in the home page. And i do this to another userform but it works fine, that's why i'm wondering what's wrong with the code so that this error happens
 
Upvote 0
Ok, missed it initially. This line
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Range("G65536").End(xlUp)).Address
should be
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Address
 
Upvote 0
Ok, missed it initially. This line
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Range("G65536").End(xlUp)).Address
should be
VBA Code:
ComboBox9.RowSource = Sheets("Activity Data").Range("G2", Sheets("Activity Data").Range("G65536").End(xlUp)).Address
I have tried this one, it enables the macro to be run from different sheet but the combobox9 only show blank choices instead of the rowsource. Any idea why could this happen?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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