[blackyroses]

Hi all.

I am using Excel VBA 2007 to create a message box, and I'm new to VBA programming But I have managed to create a simple message box with those Excel VBA books that i have borrowed. However, I need to further improve on the codes!

The eg of simple codes:

Sub ShowMessage()
    
    MsgBox ("ARFCN : 79 (E-GSM)" _
    & vbCr & "L2 Pseudo Length : 18" _
    & vbCr & "Skip indicator : 0" _
    & vbCr & "Protocol discriminator : (6) Radio resources management messages" _
    & vbCr & "Message type : 27")
End Sub

Say the user needs to double-click on a certain cell in the Excel worksheet, after which a message box will popped out to show necessary details for that particular cell. So, how do I display a lump sum of text from a .txt file or from database Access in a message box after double-clicking a cell? Anybody has got any idea?

[georgev]

You have to read the text file... Have a look at the FileSystemObject and/or TextStream in help files.

Here's something from my bookmarks that will be useful; http://www.xtremevbtalk.com/showthread.php?t=123814

[blackyroses]

Hi George, thanks for the link! I will take a look at it

[blackyroses]

Hi George, to be honest, it's more than a little over my head as I still don't understand. But if this is the only approach, then slogging through it will be a good learning experience for me I'm sure

Thanks again!

[georgev]

Give it a shot, if you can't get it to work post back your efforts and we'll work on it

[blackyroses]

Thanks ALOT for the help, appreciate it!

Here are the codes:
__________________________________________________ _______________
Sub ShowMessage()

MsgBox ("ARFCN : 79 (E-GSM)" _
& vbCr & "L2 Pseudo Length : 18" _
& vbCr & "Skip indicator : 0" _
& vbCr & "Protocol discriminator : (6) Radio resources management messages" _
& vbCr & "Message type : 27")
End Sub
__________________________________________________ _______________
Sub Open_One_Or_Many_Files()
Dim vaFiles As Variant
Dim i As Long

vaFiles = Application.GetOpenFilename _
(FileFilter:="Text files (*.txt),*.txt", _
Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

With Application
.ScreenUpdating = False
For i = 1 To UBound(vaFiles)
Workbooks.Open vaFiles(i)
Next i
.ScreenUpdating = True
End With

End Sub
__________________________________________________ _______________

The 2nd part, it allows me to open whichever text file that I would like to open, but how can edit the codes to let it open in a messagebox rather than a new Excel workbook?

And also, running the 2 parts together, my original messagebox is gone haha. Why is it so?

Sorry if it's too long or draggy, I'm new to Excel VBA

[georgev]

Can't believe I forgot this: http://www.dbforums.com/showpost.php...8&postcount=35
This utilises the FileSystemObject to grab a file, like your Application.GetOpenFilename does, however using the FSO you can limit it to only allowing a single selected file.

Anyhow, to modify your code to read the text files first line try this

vaFiles = Application.GetOpenFilename _
(FileFilter:="Text files (*.txt),*.txt", _
Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

Dim fs As Object
Dim filePath As String
Dim a As Variant
Const ForAppending As Integer = 8

    'Grab the first file from the array
    filePath = vaFiles(0)
        'MsgBox filePath

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.OpenTextFile(filePath, ForAppending)

        'Display first line
        MsgBox a.ReadLine

    a.Close
    Set a = Nothing
    Set fs = Nothing

[blackyroses]

Hi George, sorry for the late reply and thanks again!

I opened the FSO Access database, but I couldn't view the codes. And anyway, I have tried the codes that you mentioned:

__________________________________________________ ___________
Sub ShowMessage()

MsgBox ("ARFCN : 79 (E-GSM)" _
& vbCr & "L2 Pseudo Length : 18" _
& vbCr & "Skip indicator : 0" _
& vbCr & "Protocol discriminator : (6) Radio resources management messages" _
& vbCr & "Message type : 27")
End Sub
__________________________________________________ ___________
Sub Open_One_Or_Many_Files()

Dim vaFiles As Variant

vaFiles = Application.GetOpenFilename _
(FileFilter:="Text files (*.txt),*.txt", _
Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

Dim fs As Object
Dim filePath As String
Dim a As Variant
Const ForAppending As Integer = 8

'Grab the first file from the array
filePath = vaFiles(0)
'MsgBox filePath

Set fs = CreateObject("Scripting.FileSystemObject")

Set a = fs.OpenTextFile(filePath, ForAppending)

'Display first line
MsgBox a.ReadLine

a.Close
Set a = Nothing
Set fs = Nothing
End Sub
__________________________________________________ ___________

However, this appears:

Run-time error '9':
Subscript out of range

I have highlighted the error part in red. Why is it so?

[georgev]

Try vaFiles(1) instead?

[blackyroses]

I have tried, and there is a new error:
Run-time error '54':
Bad file mode.

Sub Open_One_Or_Many_Files()

Dim vaFiles As Variant

vaFiles = Application.GetOpenFilename _
(FileFilter:="Text files (*.txt),*.txt", _
Title:="Open File(s)", MultiSelect:=True)

If Not IsArray(vaFiles) Then Exit Sub

Dim fs As Object
Dim filePath As String
Dim a As Variant
Const ForAppending As Integer = 8

    'Grab the first file from the array
    filePath = vaFiles(1)
        'MsgBox filePath

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.OpenTextFile(filePath, ForAppending)

        'Display first line
        MsgBox a.ReadLine

    a.Close
    Set a = Nothing
    Set fs = Nothing
End Sub

Very sorry to trouble you again George

[georgev]

Oh dear, spotted the mistake...

Set a = fs.OpenTextFile(filePath, ForAppending)

    MsgBox a.ReadLine

We need to open the file ForReading!

[blackyroses]

Hi georgev, thanks for the help!