PDA

View Full Version : [SOLVED] Excel workbook activation



ilyaskazi
03-16-2005, 02:43 AM
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
03-16-2005, 03:25 AM
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
03-16-2005, 04:00 AM
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
03-16-2005, 04:10 AM
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
03-16-2005, 11:50 PM
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
03-17-2005, 07:58 AM
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
03-18-2005, 12:13 AM
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
03-18-2005, 10:06 AM
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
03-18-2005, 10:34 AM
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
03-18-2005, 10:37 AM
.. 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
03-18-2005, 10:55 AM
ahhh. cool :)

BlueCactus
03-18-2005, 01:06 PM
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
03-18-2005, 08:37 PM
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
03-18-2005, 09:31 PM
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! :yes

Zack Barresse
03-22-2005, 06:36 PM
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 (http://www.oaltd.co.uk/Excel/Default.htm), by Rob Bovey using Windows API. Fwiw. :)