Issues with InputBox

[infinity]

Hey everybody,

I had another thread going regarding this issue but it is not yet resolved. Could someone please look at this and give advice. What I want to do is this, when someone hits the ActiveX button for printing the letter on the sheet "DONATION LETTER", I want a message box to come up that asks if they want to print an envelope with the letter. If they say no, it just prints the letter, which works fine. If they say yes, I want to pause the macro, and bring up an InputBox for the user to enter a name and address to put on the envelope in a Text box that is on the worksheet "ENVELOPE" and then print the letter and the envelope. The problem that I am having, aside from the fact that I am new to VBA and this is my first time using InputBox is that this code keeps bringing up errors. Thanx for your time, I know it is valuable.

[VBA]
Dim Msg, Style, Title, Response, myString
Dim myRange, Cell As Range
Msg = "Would you like to print an envelope with this letter?"
Style = vbYesNo + vbQuestion + vbDefaultButton1
Title = "PRINT DONATION LETTER"
Response = MsgBox(["Would you like to print an envelope with this letter?"], _
vbYesNo + vbQuestion + vbDefaultButton1, ["PRINT DONATION LETTER"])
myString = "Yes" + "No"
If Response = vbYes Then
Sheets("ENVELOPE").Activate
Range("F13:K19").Select

myString = ""
Set myRange = Range("F13:K19")

For Each Cell In myRange
myString = Cell.Value
InputBox = Me.TextBox.Text = myString
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("ENVELOPE").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Sheets("DONATION LETTER").Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Range("A1").Select
ElseIf Response = vbNo Then
Sheets("DONATION LETTER").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Range("A1").Select
End If

End Sub
[/VBA]

Thanx Scott

[malik641]

Hey Scott, welcome to VBAX

There's a few inconsistencies I noticed with your code...
You have variables that have set values, but are never used (e.g. Title, Style, and Msg) and your use of mystring is a little difficult to follow. Not to mention only one of your variables is declared to anything, where the rest are variants (I say this because it's good practice to set variables to the proper declarations). Not a biggie seeing that you're new to VBA, can be easily fixed


But if the user wanted to print an envelope you never asked them for a name...?

Do you think you could post the workbook??? (I would try to fix this normally, but it's pretty late and I'm going to bed soon )

[infinity]

Thank you, I appreciate any help you can give. Please see my attached workbook. VBax had been a huge blessing, thank you!

[malik641]

Hey Scott

Thanks for posting the workbook. I came up with something for you, but I'm at work and I cannot compress the file to .ZIP. I'll post again when I get home...I'm pretty sure it's what you were looking for


I'll be back

[Cyberdude]

Cheers, Scott! I don't have any answers, but I don't fully understand what you are trying to do with regard to "printing an envelope". For my own education, are you trying to print directly onto an envelope, or are you going to print an address label, or what?

P.S. When you're finished, please click the "Thread Tools" at the top and mark this thread as "Solved". Thanx.

[malik641]

Hey Scott,
Check this out, I think you'll like it

[infinity]

Joseph,

Has anyone told you, you are AWESOME! This is exactly what I wanted it to do. Thank you so much. I need to go to school to learn all the ins and outs of programming, it is something that I really enjoy doing, and I am self taught at this point, but I would really like to do it for a living. Thanx again.

a very grateful Scott

[geekgirlau]

Hi Scott,

Don't forget to mark the thread as "Solved" by clicking the "Thread Tools" at the top.

[malik641]

Joseph,

Has anyone told you, you are AWESOME! This is exactly what I wanted it to do. Thank you so much. I need to go to school to learn all the ins and outs of programming, it is something that I really enjoy doing, and I am self taught at this point, but I would really like to do it for a living. Thanx again.

a very grateful Scott

Thanks Scott Glad to help!

Not bad for self taught, BTW

[infinity]

Sorry, I forgot to mark it solved! Hey Joseph, can you tell my how to add another field to the user form that comes up? I also need to change the first field to ask for the name instead of the address, then the second field to ask for the street address, the third field to ask for the city state and zip code, and the fourth field that I need to add for an additional line (maybe for a C/O somebody). Thank you again!

[mdmackillop]

Hi Scott,
A little addition. If you add a sheet called Addresses, the following addition to Josephs code will save the addresses you enter.
Regards
MD

[VBA]
AddressTXT = txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtZIPCode.Text

Dim Rw as long
With Sheets("Addresses")
Rw = .Cells(Cells.Rows.Count, "A").End(xlUp).Row() + 1
.Cells(Rw, 1).Value = txtStreet
.Cells(Rw, 2).Value = txtCityState
.Cells(Rw, 3).Value = txtZIPCode
End With

[/VBA]

[infinity]

Hi mdmackillop

Thank you for the addition. I had not thought about keeping a log of addresses that I send the donation letter to until you gave me this code. I tried to run it however and I got a compile error saying "variable not defined" it highlighted the portion of code "txtStreet". This is probably a simple fix for you, but I am so new to VBA that I am not sure what to do with it. Thank you so much, you have no idea how much VBax has helped me. I have learned so much from all of you.

[VBA]
AddressTXT = txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtZIPCode.Text

Dim Rw as long
With Sheets("Addresses")
Rw = .Cells(Cells.Rows.Count, "A").End(xlUp).Row() + 1
.Cells(Rw, 1).Value = txtStreet
.Cells(Rw, 2).Value = txtCityState
.Cells(Rw, 3).Value = txtZIPCode
End With

[/VBA]

[mdmackillop]

Oops! Worked for me but try.
[VBA]
AddressTXT = txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtZIPCode.Text

Dim Rw As Long
With Sheets("Addresses")
Rw = .Cells(Cells.Rows.Count, "A").End(xlUp).Row() + 1
.Cells(Rw, 1).Value = txtStreet.text
.Cells(Rw, 2).Value = txtCityState.text
.Cells(Rw, 3).Value = txtZIPCode.text
End With

[/VBA]

[infinity]

I tried it again and got the same compile error. The portion that is highlighted is the "txtStreet" in this portion of the code.

AddressTXT = txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtZIPCode.Tex

Thank you again.

[mdmackillop]

I hadn't changed that line, but try.

[infinity]

mdmackillop,

The attachment that you sent with letterhead-2.zip was the same code as what Joseph sent in letterhead-1.zip.

Thanx Scott

[malik641]

Hey Scott, here's a little tutorial about Userforms just to get you started.

http://spreadsheets.about.com/od/exc...xuserform1.htm

And I added what you asked for

Sorry I forgot the name should've remembered that one!
And I've added something similiar to mdmackillop's code, I just modified it so it won't repeat entries

Here is the additional code:
[VBA]Private Sub cmdOK_Click()
Dim AddressTXT As String
Dim i As Long
Dim j As Long
Static noStreet As String
Static noCity As String
Static noName As String

noName = "You have not entered a name." & vbCrLf & vbCrLf & _
"Please enter a valid name."

noStreet = "You have not entered a street address." & vbCrLf & vbCrLf & _
"Please enter a valid street name."

noCity = "You have not entered a city/state." & vbCrLf & vbCrLf & _
"Please enter a valid City/State."

If txtName = vbNullString Then
MsgBox noName, vbOKOnly, "Name"
txtName.SetFocus
Exit Sub
ElseIf txtStreet = vbNullString Then
MsgBox noStreet, vbOKOnly, "Street Address"
txtStreet.SetFocus
Exit Sub
ElseIf txtCityState = vbNullString Then
MsgBox noCity, vbOKOnly, "City/State"
txtCityState.SetFocus
Exit Sub
End If

AddressTXT = txtName.Text & Chr(10) & txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtAdditional.Text

Sheets("ENVELOPE").Shapes("txtAddress").TextFrame.Characters.Text = AddressTXT

With Sheets("Addresses")
i = .Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To i
If .Cells(1, 1).Value = vbNullString Then
.Cells(1, 1).Value = txtName.Text
.Cells(1, 2).Value = txtStreet.Text
.Cells(1, 3).Value = txtCityState.Text
.Cells(1, 4).Value = txtAdditional.Text
Exit For
End If

If .Cells(j, 1).Value <> txtName.Text Or _
.Cells(j, 2).Value <> txtStreet.Text Or _
.Cells(j, 3).Value <> txtCityState.Text Or _
.Cells(j, 4).Value <> txtAdditional.Text Then
.Cells(j + 1, 1).Value = txtName.Text
.Cells(j + 1, 2).Value = txtStreet.Text
.Cells(j + 1, 3).Value = txtCityState.Text
.Cells(j + 1, 4).Value = txtAdditional.Text
Exit For
End If
Next j
End With

PrintEnvelope.Hide
End Sub
[/VBA]

[malik641]

OOPS! That extra code didn't work right my bad!

Here ya go I think this is it....

[VBA]Private Sub cmdOK_Click()
Dim AddressTXT As String
Dim i As Long
Dim j As Long
Dim newCount As Long
Static noStreet As String
Static noCity As String
Static noName As String

noName = "You have not entered a name." & vbCrLf & vbCrLf & _
"Please enter a valid name."

noStreet = "You have not entered a street address." & vbCrLf & vbCrLf & _
"Please enter a valid street name."

noCity = "You have not entered a city/state." & vbCrLf & vbCrLf & _
"Please enter a valid City/State."

If txtName = vbNullString Then
MsgBox noName, vbOKOnly, "Name"
txtName.SetFocus
Exit Sub
ElseIf txtStreet = vbNullString Then
MsgBox noStreet, vbOKOnly, "Street Address"
txtStreet.SetFocus
Exit Sub
ElseIf txtCityState = vbNullString Then
MsgBox noCity, vbOKOnly, "City/State"
txtCityState.SetFocus
Exit Sub
End If

AddressTXT = txtName.Text & Chr(10) & txtStreet.Text & Chr(10) & txtCityState.Text & Chr(10) & txtAdditional.Text

Sheets("ENVELOPE").Shapes("txtAddress").TextFrame.Characters.Text = AddressTXT

With Sheets("Addresses")
i = .Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To i
If .Cells(1, 1).Value = vbNullString Then
.Cells(1, 1).Value = txtName.Text
.Cells(1, 2).Value = txtStreet.Text
.Cells(1, 3).Value = txtCityState.Text
.Cells(1, 4).Value = txtAdditional.Text
Exit For
End If

If .Cells(j, 1).Value <> txtName.Text Or _
.Cells(j, 2).Value <> txtStreet.Text Or _
.Cells(j, 3).Value <> txtCityState.Text Or _
.Cells(j, 4).Value <> txtAdditional.Text Then
newCount = 1
Else
newCount = 0
End If
Next j

If newCount = 1 Then
.Cells(j, 1).Value = txtName.Text
.Cells(j, 2).Value = txtStreet.Text
.Cells(j, 3).Value = txtCityState.Text
.Cells(j, 4).Value = txtAdditional.Text
End If

End With

PrintEnvelope.Hide
End Sub
[/VBA]