PDA

View Full Version : Input Box



infinity
11-04-2005, 07:18 PM
Hi all you coding pros

I am trying to use an input box to enter a name and address on a different sheet within the workbook if the response to the MsgBox = vbYes. At the point in the code where it says

Sheets("ENVELOPE").Activate
Range("F13:K19").Select

I would actually like to put the address into a text box if possible. Could someone look at my code and offer any suggestions? Thank you so much!


Dim Msg, Style, Title, Response, MyString
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
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

Sir Phoenix
11-04-2005, 07:56 PM
Let me suggest this:

dim myRange, Cell as Range
dim myString as String

myString = ""
set myRange = .range("F13","K19")

for Each Cell in myRange
myString += Cell.Value


This will concatenate all the values of each cell into one string, and then with your input box, use the code:

me.textBox.text = myString

to put the values from the cells into the textBox.

infinity
11-04-2005, 08:45 PM
Thank you Sir Phoenix for your response. I am fairly new to VBA and this is the first time I am using an InputBox. I placed your code within my code but I got an error that read "Duplicate declaration in current scope". I also am not sure what you mean by "with your input box, use the code me.textBox.text = myString". I really appreciate your help. Below I am placing the updated code with yours.

Dim Msg, Style, Title, Response, myString
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
Dim myRange, Cell As Range
Dim myString As String

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

Sir Phoenix
11-04-2005, 09:10 PM
The parts that begin with dim, put those at the top. dim's are your declarations, and you can only declare them at the top. If you put more in the middle, it thinks you forgot to close a method and are starting a new one improperly.

infinity
11-04-2005, 09:22 PM
I placed the Dim statements at the top of the code and I am still getting the same error. The other thing is could Is my InputBox code correct or would you give me the code I would use to bring up the InputBox. The text box on my sheet that I want the result of the InputBox to go into is TextBox25_Click. Thank you for your time.

MWE
11-04-2005, 10:18 PM
Let me suggest this:

dim myRange, Cell as Range
dim myString as String

myString = ""
set myRange = .range("F13","K19")

for Each Cell in myRange
myString += Cell.Value


I have never seen the syntax myString += Cell.Value before. I assumed that it was the equivalent to myString = myString & Cell.Value until I tested it and received an immediate syntax error. :dunno