Fill Userform textbox after clicking in sheet (return column number)

[danovkos]

Hi,
i try to figured out, how to fill my textbox, after clicking in the sheet.
I want to return always actual number of column.

e.g.
Userform is initialized, textbox1 is focused, then i click to column B and it immediately return in textbox1 number 2.
Then i select textbox 2 and click to sheet in column C and it immediately return in textbox2 number 3....and so...

Pls. any suggestions?
I tried it found with google, but i was not succsessfull.
thx

[EirikDaude]

Would something like this work? Untested, but I'd think it should do what you want?

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not IsUserFormLoaded("uform1") Then
    uForm1.Load
  End If
  uForm1.txtbox1 = Target.Column
  uForm1.Show
End Sub

 '===
 ' gijsmo April 24th, 2011; http://www.ozgrid.com/forum/showthread.php?t=152892
Function IsUserFormLoaded(ByVal UFName As String) As Boolean
    Dim UForm As Object
    For Each UForm In VBA.UserForms
        IsUserFormLoaded = UForm.Name = UFName
        If IsUserFormLoaded Then
            Exit For
        End If
    Next
End Function 'IsUserFormLoaded

[danovkos]

I should insert whole code,to userform modul, or privatesub to Worksheet modul?
Will it also works, if i want to click to TB1, TB2....?
thx for your help

[EirikDaude]

The code inside the "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" needs to be in the worksheet-code for the sheet you can click on, the function I'd put in a standard module. Obviously you need to make sure the name of the userform (uform1) and textbox (txtbox1) needs to be changed to the actual names you are using in your project.

It won't work if you want to write to different textboxes, the code above will only write to txtbox1. It should however be *fairly* trivial to add in an if clause or something like that to choose a different textbox each time. What do you want to be the deciding factor for what textbox the event writes to, and how are your textboxes named?

[danovkos]

My textbox names are

TB_odkial_nazov
TB_odkial_podla
TB_odkial_vracaj

TB_kam_nazov
TB_kam_hodnota
TB_kam_podla

And deciding factor for what textbox the event writes to is,
when i click to TB it writes column number from selected cell in sheet.
Only fyi.
I want automate my often compare and filling data tables.
So i select one sheet, then i click to column with ID or names (base this ID compare), then i click to blank column (here i want insert data), then i click to other sheet, wb ..., and i choose source column with IDs, and finely click to column with data, which i want to fill in my blank column. Maybe so dummy, but it helps me a lot.

[EirikDaude]

I am not entirely sure I understand. In what cases do you want to write the column number to TB_odkial_nazov, when to TB_odkial_podla and so forth?

[danovkos]

Sorry i describe it in other words.
1 TB (nazov) - insert not column, but caption of WB, then 1 column for ID (KDE), 2nd column for data, where will be filled data what macro found in Workbook2. This Workbook2 will be defined with those othere 3 TB KAM. One TB for name of WB, 2 for columns. First column where it will looking for ID (from TB KDE), and second where it will looking for for values..
Result wil be, that my first workbook with blank column will be filled with data from my WB2. I know, my english and explanations are very bad, so i understand, that it is hard to understand me.

[danovkos]

Now i go to launch, i will back in 1 hour. Thx a lot for your help

[EirikDaude]

Hmm, the problem then is that you'd need the code in both workbooks, in the worksheets you want to copy / , and I think you'd need to name the workbook the userform is located in when writing to it from a different workbook.

Apart from it shouldn't be too hard to modify the code I posted above to suit your needs.

Doing some googling on my own I found this site: http://peltiertech.com/WordPress/ref...l-alternative/ - I think that may describe something similar to what you are looking for too? And probably less prone to failing than my untested code :-D

Anyway, if your goal simply is to copy from one workbook to another, I am not sure if going via an userform necessarily is the best idea, but it's a bit hard to tell without knowing the details

I'm happy to help, and (I think) your English was more than good enough to get you the help you wanted

[danovkos]

Ok, ill try. Thx for help. Bye.

[EirikDaude]

I hope it works out for you