Cannot switch between Userform to Excel Workbook

[ilyaskazi]

I hv created Userform (maximize startups window screen). In this Userform there are several objects such as textbox. Now I need to copy data from excel to Userform. Problem is I cannot switch between this Userform and any open Excel workbook.

If I minimize the screen manually of Userform, then I m able to copy data of excel and then again maximize the screen of Userform to paste the same.

But this is not the solution. I want switch method i.e. "ALT + TAB" only.

plzzzzzzzzzz help.: pray2:

[Killian]

You can either write some routines that select the ranges you want to pass from the worksheet to the user form or, if you're on Office2000 up, use a modeless userform (set the Userform's ShowModal property to false)

[ilyaskazi]

Hi Killian,

thanku 4 replying. I m using office xp. ShowModal property is already set to false. I cannot write any routines which will select the ranges to pass the data bcoz ranges always keep changing...

any other solution plz... awaiting.

[johnske]

Hi Killian,

thanku 4 replying. I m using office xp. ShowModal property is already set to false. I cannot write any routines which will select the ranges to pass the data bcoz ranges always keep changing...

any other solution plz... awaiting.


Hi iltaskazi,

It doesn't matter if the ranges keep changing... Paste this into your userform module, select a cell with something written in it and show your userform. Now select another cell and left-click inside the text-box on the userform...etc.


Option Explicit

Private Sub UserForm_Activate()
TextBox1 = Selection
End Sub
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox1 = Selection
End Sub

Regards,
John

[ilyaskazi]

Hi Killian,

ur solution is good. It works while copying a single cell only. I need more than single cells, either selecting coloumn or row.

thanku :yes

[johnske]

Hi iltaskazi,

OK, try this...(If you have the code above in the userform module, first get rid of it or comment it out to prevent errors).

Show the Userform, select the range you want to copy, press Ctrl+C then select the text box and press Ctrl+V, this will paste your selection into the text box. You can also do this with code, a basic way to do this is use this in the Userform module:


Option Explicit

Private Sub UserForm_Activate()
Range("D1:D6").Select
Selection.Copy
TextBox1.Paste
End Sub

HTH,
John

[ilyaskazi]

Hi Killian, John,

Thanku very much... It was good. All is working fine.

Thanku once again.:thumb

[johnske]

Hi iltaskazi,

Not a prob., just glad to see you've got it sorted.

John :hi:

(PS You can mark you own thread solved by clicking thread tools (above) then check Mark solved and click Perform Action...)