Imput Box Question

D

Db1712

Is there a way to have an imput box, with two value line imputs tha
opens when e.g sheet1 opens. The value of line 1 is then linked t
Sheet2 Range A1. The value of line 2 is linked to Sheet2 Range B1.
Also once a value is assigned to the linked cells via the imput box
the imput box doesn't re-open when accessing the sheet it is assigne
to. Thanks in advance for any assistance with this.

Imput Box Example
Enter Sales ________
Enter Previous Sales _______
 
D

Dave Peterson

You could create a little userform with the two textboxes and a couple of
buttons on them (ok/cancel).

Then have the code check to see if the cells are empty before showing the
userform.

In a general module:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
If Trim(.Range("a1").Value) = "" _
Or Trim(.Range("b1").Value) = "" Then
UserForm1.Show
End If
End With
End Sub

In the userform module:

Option Explicit
Private Sub CommandButton1_Click()

If Trim(Me.TextBox1.Text) = "" _
Or Trim(Me.TextBox2.Text) = "" Then
MsgBox "Please enter values in both text boxes"
Exit Sub
End If

With Worksheets("sheet1")
.Range("a1").Value = Me.TextBox1.Text
.Range("b1").Value = Me.TextBox2.Text
End With

Unload Me

End Sub
Private Sub CommandButton2_Click()
'hit the cancel key
Unload Me
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you're new to userforms, visit Debra Dalgleish's site:
http://www.contextures.com/xlUserForm01.html
 
Top