Dual entering

[Hillsy7]

Hi there.

I have an issue at the moment with a Listbox linking through to a textbox on a userfrom

Doubleclicking the textbox opens a 2nd Userform containing a simple list box.....double clicking the selected item populates the orignal textbox & closes the 2nd Userform....simple right?

Although it writes to the original textbox fine, it also rights to the ActiveCell on the spreadsheet behind the Userform.....

Here's the code

DataEntry.ActiveControl.Value = selection

The problem is I can't be more specific....there's 15 such textboxes that all use this 2nd Userform so I can't specify which textbox will need populating

Any Ideas?

[tigeravatar]

Hillsy7,

I would recommend instead of the second userform, make each textbox be a combobox so users can select each item from a drop-down list. As far as inputting data into the active cell:

ActiveCell.Value = Me.ActiveControl.Value

Hope that helps,
~tigeravatar

[Hillsy7]

Hi Tiger

I thought about using ComboBoxes but I may also need sometimes for it to be manually entered as well and to actively prevent the ComboBoxes from working when a certain condition is met, allowing manual entry only

The thing is I DON'T want the value written to Any Cells as it might overwrite data already there.....but the active control thing seems to think both the last cell selected before the userform was opened AND the text box I clicked through are both active.

Surely they's a way of targetting the last active textbox?

grrrrrr

[tigeravatar]

Hillsy7,

Alright, so I think I understand the issue now. The problem is that its dual-entering and you do not want it to input the information anywhere other than the userform. In my test workbook (which is attached), I did the following to get it to work:

-Created a module that contains two public variables: txtActive and lbxSelection
(txtActive will be the active textbox name that got double-clicked and lbxSelection will be the double-clicked value of the listbox)
-Created a function that gets called when a textbox on the first userform is doubleclicked
-The function sets the txtActive public variable to the active textbox's name and then opens the second userform.
-The second userform sets the double-clicked value of the listbox to the lbxSelection public variable
-The function then makes the txtActive textbox text = lbxSelection.

See the attached workbook to see how it works. Let me know if you're looking for something different and I didn't actually understand your issue.

Hope that helps,
~tigeravatar