Simply show UserForm

[KIM W]

How can I most simply have user display a userform with the following
requirements:
1. I don't want them to have to learn and remember keyboard shortcut
2. Clicking on worksheet is desirable solution
3. User may rearrange and add columns
4. User may change width of columns
5. Userform is relevant to only SOME columns, so if it were a button it
would only be on 10% of the columns in a many column worksheet (not designed
to print)
6. Same userForm is launced regardless of column one is working in.
7. BTW, this UserForm ends up writing text into the active cell.

Thanks in advance.

 

[Stringer]

Create a button, right click on it and select Assign a macro, then
select new, you will then be taken to VBA and in the Button Click code
enter.

userform1.show

Go back to excel and click the Button, your UserForm will Show. (If it
is named UserForm1)

As far as the UserForm goes. You will have to create one first, then
come back to the forum and ask questions

Here's a good start

'Excel -- UserForms -- Create a UserForm'
(http://contextures.com/xlUserForm01.html)

 

[KIM W]

Thanks-- I already did that. I am in 2007.
DO you recommende ActiveX control on the worksheet for the button, or User
Form Control on the worksheet? I am particularly concerned about the button
staying with the column as things change in the worksheet.
Userform has already been created and works fiine.

 

[Neptune Dinosaur]

Kim

If you embed a button directly on the sheet, you can set its properties such
that it will not move or stretch regardless of what people do to the rows &
columns.

Right cluck on the button >> Format Control >> Properties >> "Don't move or
size with cells".

 

[Stringer]

Thanks-- I already did that. I am in 2007.
DO you recommende ActiveX control on the worksheet for the button, or
User
Form Control on the worksheet? I am particularly concerned about the
button
staying with the column as things change in the worksheet.
Userform has already been created and works fiine.

You could use a right click event so when the user right clicks a
certain column the userform will show.
Eaxample

Code:
--------------------
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
if target.column=1 then
cancel=true
userform1.show
end if
End Sub

--------------------

The when you right click anywhere on Column A the userform will show.

If you use a button..
Any button will do, I personally like the Button from the Forms tool
bar.

Lots of worksheets require the top rows to be frozen(so you can always
see the Column Headings), if this is the case, you can place the button
on the frozen rows, then it will always be accessible.

 

[KIM W]

I am finding that I cannot get an ActiveX comman button to re-size with cell.
It is the behavior I want-- button to stay with cell no matter what.
(Opposite of what you suggest below.) A UserForm comman button on the sheet
cannot be re-colored, so I don't want that.

 

[KIM W]

Thanks. I'll see how that feels.

You could use a right click event so when the user right clicks a
certain column the userform will show.
Eaxample

Code:
--------------------
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
if target.column=1 then
cancel=true
userform1.show
end if
End Sub

--------------------

The when you right click anywhere on Column A the userform will show.

If you use a button..
Any button will do, I personally like the Button from the Forms tool
bar.

Lots of worksheets require the top rows to be frozen(so you can always
see the Column Headings), if this is the case, you can place the button
on the frozen rows, then it will always be accessible.

 

[exceluserforeman]

DataPanels_XP.xls created: Wednesday,9 November 2005 Excel Xp
Similar to DataPanels.xls but a bit more sophisticated. The user uses the
scrollers to navigate around the Active Worksheet. You can enter text in any
cell, except Column 1 and Row 1 by way of a textbox on the userform. I have
incorporated the use of Dynamic Text & Interior Color Scrollers. The User
cannot change anything in Column A or Row 1 unles you use the Builtin Dialogs
buttons. The Builtin Dialogs buttons can also be used to change the Font and
Interior and Pattern Colors.

Click the label "Data Panels" in A1 to start up userform.

You can modify it to suit.

Yo may also want to implement a toolbar nutton rather than an object on the
sheet to initiate the userform

http://www.geocities.com/excelmarksway