Excel workbook activation

[ilyaskazi]

i hv created userform in Excel VBA in which textboxes and other such objects are made. Now I want to copy data from excel sheets to userform manually by copy-paste shortcut keys. Problem is when userform is active, all the workbooks are then deactivated until it is open.
I want both the workbook and as well as userform to be active, so that I can copy-paste my excel sheets data to userform.

Plz solve this at ur earliest.

[johnske]

Hi ilyaskazi,

Welcome to VBAX. To make both the worksheet and userform active requires Office 2000 or greater. By default your userform is shown modally (which is what you've described) - put False after your "Show" command e.g.:

Userform1.Show False

to show it non-modally.

If you have Office '97 this will not work (although there is another method to simulate non-modal userforms in 97)

HTH
John

[ilyaskazi]

Dear John,

thanku 4 repling, but the problem is not solved. I m using office XP.

My form name is "Rules" (userform)

While...

Private Sub UserForm_Initialize()
Dim hWnd As Long
hWnd = FindWindow(vbNullString, Rules.Caption)
SetWindowLong hWnd, -16, &H20000 Or &H10000 Or &H84C80080
Userform1.Show False
End Sub

I get runtime error="424" telling object required.
awaiting..

[johnske]

Dear John,

thanku 4 repling, but the problem is not solved. I m using office XP.

My form name is "Rules" (userform)

While...

Private Sub UserForm_Initialize()
Dim hWnd As Long
hWnd = FindWindow(vbNullString, Rules.Caption)
SetWindowLong hWnd, -16, &H20000 Or &H10000 Or &H84C80080
Userform1.Show False
End Sub

I get runtime error="424" telling object required.
awaiting..

Hi ilyaskazi,

In that case it would be...

Rules.Show False

But this goes into a standard module (delete the Userform1.Show False from where you have it above).

In the VBE window got to Insert/Module then paste this code in the module

Sub ShowForm()
Rules.Show False
End Sub

exit the VBE window and run the code from Tools/Macro/Macros../ShowForm/Run

John

[ilyaskazi]

Hi John

Ur solution to my problem is solved. Thanku. But I cannot switch between my userform window & excel workbook. My Userform opens up in maximize mode. Also when pasting data from Cell, it also pastes ?Enter Note/Symbol? after the value in the textbox.

[Zack Barresse]

Sounds like you want a vbModeless userform?? Not sure if I fully understand you.

Rules.Load
Rules.Show vbModeless

Note this does NOT work in Excel 97, as it doesn't support this method; only in 2000 and greater. You can transversley use vbApplicationModal and vbSystemModal to NOT allow user access to either just the Application or the entire System (other Applications) until the Userform is dealt with.

[ilyaskazi]

firefytr,

Ur solution is not working. I m using office xp. When I activate Userform (which is maximized), the problem is I cannot switch between my Userform and any open exel workbook by using "ALT + TAB" method.

Other solution plz..

[Ken Puls]

Hi there,

I wouldn't expect to be able to use that key combination by changing any of the userform properties. Alt+Tab is a windows shortcut to switch between applications. As the userform is part of the application, it wouldn't be picked up by windows.

I'm sure that we could attempt to overrule the keystroke combination, but have never tried superseeding a windows combination, so am not sure if it would work or not...

[sandam]

If your form is displayed by triggering events you can have a button on th eform that hides it and use

Private Sub cmdHideButton_Click()
    UserForm1.Hide
  End Sub

And then in your book have an event proedure that triggers the user form to show again

Sub SomeEventInExcel
  Userform1.Show
End Sub

but remember that a userform loses its variable information when hidden.

[Zack Barresse]

.. but remeber that a userform looses its variable information when hidden.

Just as a side note, this can be thwarted. Check out the use of Public/Global variables and the Static method.

[sandam]

ahhh. cool

[BlueCactus]

1. Why post the same question twice?

Edit: link added - http://www.vbaexpress.com/forum/showthread.php?t=2354
(Maybe I'm just being grumpy, but I hate to see everyone working twice to solve the same issue.)

2. What's wrong with using a RefEdit control in the userform to pick up the range of cells you need?

[ilyaskazi]

hi everybody..

Thanku for solving the problem. All is working fine. Kpuls u r absolutely right but thing is I need the solution and for the same Mr. Sundam has solved with alternate solution.

Thanku everybody.

and yes "mr bluecactus" ... I will take the notice of the same..

[Ken Puls]

Hi Ilyaski,

So it is solved for you then? Did you know you can mark your own posts solved here? Just see the instructions in my signature!

[Zack Barresse]

Just FYI ...

... Note this does NOT work in Excel 97, as it doesn't support this method; only in 2000 and greater. You can transversley use vbApplicationModal and vbSystemModal to NOT allow user access to either just the Application or the entire System (other Applications) until the Userform is dealt with.

... I stand corrected. This is a situation in which a userform can be shown Modeless in Excel 97 (dialog sheets in Excel 95) found here, by Rob Bovey using Windows API. Fwiw.