Calendar style function to pre-populate a userform

[ramridge]

I have a VBA userform where I am asking users to enter values in 3 text boxes, a sheetname, the x and y coordinates of a particular cell of that worksheet(same workbook). These values are stored in a database for later processing.
Methinks a smarter way would be something similar to the calendar function on websites say for hotel reservations. You click on an icon, up pops a calendar, click on a date and the value is populated in the appropriate box. No messing, no guessing. Using this analogy, I want something like 1. pick a sheet from a listbox on the userform, 2.click on a button, 3. up pops the sheet, 4. click on the desired cell , then 5. the sheetname, and the x and y coordinates are automatically populated in the 3 textboxes on the userform.
Is this stretching the boundaries of Excel?

 

[bertie]

Insert a combobox on your form and name it cboSheets.

You can populate this via the userform_initialize event:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
 
   [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] Worksheets
      Me.cboSheets.AddItem ws.Name
   [COLOR=darkblue]Next[/COLOR] ws
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Then add a Change event to the combobox:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cboSheets_Change()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
 
   Me.Hide  [COLOR=green]'hide the userform[/COLOR]
   Worksheets(cboSheets.Value).Activate
 
   [COLOR=darkblue]Set[/COLOR] rng = Application.InputBox("Select cell", Type:=8)
 
   [COLOR=darkblue]With[/COLOR] Me
      .[COLOR=red]TextBox1[/COLOR].Value = cboSheets.Value
      .[COLOR=red]TextBox2[/COLOR].Value = rng.Row
      .[COLOR=red]TextBox3[/COLOR].Value = rng.Column
      .Show
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[ramridge]

Hi Bertie -
Fantastic. Just the ticket. You are the man.
Ram.