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?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Just change the sheet name, to the sheet you want it to work on.
 

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Just change the sheet name, to the sheet you want it to work on.
Unfortunately the sheet contains data i needed and i cant put the command button on the same sheet as the data
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
I'm sorry but that doesn't make any sense to me. What exactly are you trying to do?
 

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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
 

Enjoylyfe

New Member
Joined
Apr 29, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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