Userform problems when 2 or more workbooks open

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
You do not need to select a Workbook to enter text if the Workbook is open.

Use a script like this:

Code:
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

Active Member
Joined
May 26, 2010
Messages
320
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
May 26, 2010
Messages
320

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
Workbooks("Student Subjects.xlsm").Sheets(1).Cells(1, 1).Value =Userform1.TextBox1.value
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,794
Office Version
  1. 2013
Platform
  1. Windows
I would try that and I would think that should work:
You said:
thisworkbook.Sheets("Sheet5").Range("a5") = "cake"
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
320
I was hoping there was an easier way especially with big userforms with loads of buttons and textboxes
 

Watch MrExcel Video

Forum statistics

Threads
1,108,507
Messages
5,523,308
Members
409,509
Latest member
CheekyDevil2386

This Week's Hot Topics

Top