Creating a form on a worksheet - Can I make a button for "notes"

C

cho

Is it possible to have a button on a worksheet and just have the buttons say
"notes". Then when it's pressed a dailog box opens for the data entry?. If
so, how is this done, which kind of control should I use, etc.?
 
Z

Zack Barresse

Hi,

You would best be to post this in the excel.programming newsgroup, as I
suspect you'll need VBA.

I guess, to not leave you hanging, I would do something like this ...

From Excel, hit Alt + F11, then press Ctrl + R.
Select your file (bold name) on left.
Select Insert (menu) | Module
Copy paste the code below there.
Press Alt + Q to return to Excel.
From the Forms toolbar, select a Command Button and create it.
An 'Assign Macro' dialog will appear, select 'InsertNote' and click Ok.
Save before running anything.

Code to copy/paste:

Option Explicit
Sub InsertNote()
Dim rng As Range
Dim strNote As String
Set rng = Selection
If rng.Cells.Count > 1 Then
MsgBox "Please select only one cell."
Exit Sub
End If
If Len(ActiveCell) > 0 Then
If MsgBox("There is already data in this cell." & vbNewLine & _
"Overwrite?", vbYesNo, "Overwrite?") = vbNo Then Exit Sub
strNote = InputBox("Enter new cell value:", "Enter Value")
If strNote <> "" Then ActiveCell.Value = strNote
End If
End Sub

Note that this will not leave a cell blank if nothing has been typed into
the InputBox. If you would like to add such functionality, replace the
bottom two lines with this one line ...


ActiveCell.Value = InputBox("Enter new cell value:", "Enter Value")

If you do this, you can take out this line as well ...

Dim strNote As String

HTH
 
C

cho

Wow,

I haven't used excel for a few years now. I didn't think it would be this
involved! I'll take this see what happens and hopefully if not I change or
modify whatever needs fixin' to get it to work

Thank, Paul
 
Z

Zack Barresse

Paul,

I guess some may think it a little 'over-the-top'. I use a lot of VBA
throughout the day for my job, so naturally that is what I jump to first.
For me, this just seems the simplest and fastest way to give the the exact
results you requested. I'm sure there are many other ways. I apologize if
it was too lenghty.
 
Top