How to transfer the data displayed in the msgbox onto the excel sheet?

[roopa m j]

Hi all,

I have a small requirement.

I have written a macro code in VBA excel, where I have used a userform an option buttons to choose a certain type of data
once It is chosen, a msgbox is made to display certain results choosing data from the excel sheet.
This has wonderfully worked.

now the requirement is that, whatever gets displayed on the msgbox, either needs to be printed using a print option or the data displayed should be saved in the excel sheet or a new workbook.

Is this possible to be done?

I tried a lot of options where I can transfer data from INPUTBOX to an excel sheet. but I could not get a solution for doing the same for msgbox.

any suggestions?

kindly let me know.

regards,
Roopa.

 

[Andrew Poulsom]

What did you try? If you have built a string and assigned it to the MsgBox you can assign it to a range on a worksheet.

 

[ParamRay]

.
.

Look at the line of code that calls the MsgBox function. The first argument is the prompt, which is the string expression displayed in the message box...

 

[roopa m j]

Hi,

I have just tried out this simple msgbox syntax.

for example:

Msgbox ("total number of workpackages = " & _
worksheets("Effort catalogue").range(("GL40").value & newvbline & _
"(efforts")

I do not know how to assign this to a string.

it would be a great help if this works out.

thank you.

 

[Andrew Poulsom]

Please post your existing code that shows the message box.

 

[ParamRay]

.
.

Suppose your line is:

MsgBox ("The quick brown fox jumps over the lazy dog").

Whatever you have between the parentheses (before the first comma, if there is one) is the prompt. It can be assigned to a string variable if you wish.

In my example, you could achieve this using these two lines:

Dim Txt as String
Txt = "The quick brown fox jumps over the lazy dog"

Perhaps you can adapt this simple example to suit your requirements...

 

[roopa m j]

Thanks a lot @gpeacock it worked.

 

[roopa m j]

Although, this works for a simple msg.

but when the msgbox involves a value to be picked from a cell like in the code inserted above, how will the string declaration take that value from the sheet?

 

[vds1]

To assign values from sheet cell to Variable , try

[B]Dim Txt as String[/B]
Txt= sheets("Sheet_Name").Range("A1").Value
msgbox txt