Userform problems when 2 or more workbooks open

[rhombus4]

When using a userform and you have to select a different workbook to enter data and then go back to the userform it causes errors when clicking any buttons or enter text.

Only workaround I found us activate this workbook for every button on the userform. Is there an easier way to do this.

 

[My Aswer Is This]

You do not need to select a Workbook to enter text if the Workbook is open.

Use a script like this:

Private Sub CommandButton3_Click()
'Modified  3/24/2019  8:04:56 AM  EDT
Workbooks("Student Subjects.xlsm").Sheets(1).Cells(1, 1).Value = "Cake"
End Sub

 

[rhombus4]

I don't want to enter info in the 2nd workbook using the 1st workbook(contains the userform.)

When I go into the 2nd workbook and do something in it like enter, copy or delete data, if effectively activates it.

So when I go back to the 1st workbook with the user form open and try and use it, it bugs because the second workbook is open.

 

[My Aswer Is This]

When I try what your doing all works well.

Now if you have a Userform open and try to select a cell in another workbook you will get a error.
Unless you open your Userform like this: UserForm1.show Modeless

If your opening your Userform in modeless mode and have a problem. Show me a example of a script your trying to run which causes you a error.

 

[rhombus4]

sorry for the Delay

I open userform in modeless
UserForm1.Show vbModeless

sometimes get error, Run-time error '424' Object required or Run-time error '9': Subscript out of range

Sometime have to do it 2 or 3 times when switching between the two workbooks

Private Sub OptionButton1_Click()
Sheets("Sheet5").Range("a5") = "Enter Data Here" (Workbook 2 doesnt have a Sheet5)

'or sometimes use
[MyRange].Offset(2, 1) = "Test Date" 'MyRange is just a cell value
End Sub

Only alternative I can find is
ThisWorkbook.activate at the start of every button, checkbox, textbox etc

 

[My Aswer Is This]

I would like to see a exact example of a script your having problems with.

I gave you a exact example of mine

In your case where your dealing with two different workbooks for now you should use a example just like mine. In mine I specified the exact Workbook name the exact sheet name and the exact range.

Now if you want to copy a control value from a Userform you would also need to specify the exact control name like:

Workbooks("Student Subjects.xlsm").Sheets(1).Cells(1, 1).Value =Userform1.TextBox1.value

 

[My Aswer Is This]

Still not sure why your wanting to jump back and forth from one workbook to another but you must have your reasons.

You can write a script to do almost anything you want to do without having to activate a sheet or a workbook.

And you hardly ever need to write a script which says activate.

A lot of people think you must write scripts like this:

Workbooks1. activate sheets2.activate RangeA1 .select select="Me"

That's not a good way to write scripts even thought it works.

You can write one like mine in my previous post which activates nothing.

But now if you want to switch back and forth manually between Workbooks manually and then use script in each different Workbooks you will always need to be exactly specific what workbook what sheet and what rang and what Userform and what control.

Now as you learn more there may be ways to shorten these scripts using With for example.

 

[rhombus4]

The reason some people switch between the workbooks is to get or enter other information into the other spreadsheet.

What I think is happening is, when they enter information in the other workbook, that workbook is then the active workbook.

When they then click on the userform which is open and then try to click on an option button (see below for script) it causes an error as it tries to put the information in sheet5 of the other workbook which it can't do as that workbook doesn’t have a sheet5

Sheets("Sheet5").Range("a5") = "cake"

can't use Workbooks("Student Subjects.xlsm").Sheets(1).Cells(1, 1).Value = "Cake" as the workbook name sometimes changes for different versions so I'm assuming would have to use.

thisworkbook.Sheets("Sheet5").Range("a5") = "cake"

 

[My Aswer Is This]

I would try that and I would think that should work:
You said:

thisworkbook.Sheets("Sheet5").Range("a5") = "cake"​

 

[rhombus4]

I was hoping there was an easier way especially with big userforms with loads of buttons and textboxes