Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 2nd 13, 01:35 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Wink Msg Box Title

Totally new to VBA. I have pasted a VBA code and was wondering how I can keep the MsgBox Title consistent throughout the VBA procedure. I have the following code but when the ans = vbno, the MsgBox Title is "Microsoft Excel" and not "Metals Inventory Database". Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("F2").Value 0 Then
Msg = "You owe a balance due to Virginia at this time!"
Msg = Msg & vbLf & vbLf
Msg = Msg & "Would you like to fufill that obligation"
Msg = Msg & " with a Funds Transfer?"
Title = "Metals Inventory Database"
Config = vbYesNo + vbQuestion
Ans = MsgBox(Msg, Config, Title)
If Ans = vbYes Then Workbooks.Open ("C:\Documents and Settings\My Folder\My Documents\TestII.xlsx")
If Ans = vbNo Then MsgBox ("Then please do a Journal Voucher. Thank you.")
Else
MsgBox ("No Blance Owed")
End If
End Sub


Thank you

  #2   Report Post  
Old March 2nd 13, 07:45 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 279
Default Msg Box Title

In message of Sat, 2 Mar 2013
01:33:02 in microsoft.public.excel.newusers, Gene Haines <Gene.Haines.b7
writes

Totally new to VBA. I have pasted a VBA code and was wondering how I
can
keep the MsgBox Title consistent throughout the VBA procedure. I have
the following code but when the ans = vbno, the MsgBox Title is
Microsoft Excel and not Metals Inventory Database. any help would be
greatly appreciated.


The OP might like to adapt what I show below AS I have adapted what he
has written.

Option Explicit ' All modules should start so to avoid some bugs


Private Function MIDMsg(ByVal Msg As String, Optional ByVal Config As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function

Public Sub foo()
Dim Msg As String

If True Then
Msg = "You owe a balance due to Virginia at this time!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with an Funds Transfer?"
' Brackets in call statements seem odd to me
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
' Use call if you really want to have brackets
Call Workbooks.Open("C:\Documents and Settings\MyFolder\My Documents\TestII.xlsx")
Else
' Or leave them out for textual economy.
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
' I have corrected a slip.
MIDMsg "No B[a]lance Owed"
End If

End Sub

Thank you


You're welcome. I found your problem entertaining.
N.B. my long lines may wrap in an unfriendly way.
--
Walter Briscoe
  #3   Report Post  
Old March 2nd 13, 07:21 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default

Quote:
Originally Posted by Walter Briscoe View Post
In message of Sat, 2 Mar 2013
01:33:02 in microsoft.public.excel.newusers, Gene Haines <Gene.Haines.b7
writes

Totally new to VBA. I have pasted a VBA code and was wondering how I
can
keep the MsgBox Title consistent throughout the VBA procedure. I have
the following code but when the ans = vbno, the MsgBox Title is
Microsoft Excel and not Metals Inventory Database. any help would be
greatly appreciated.


The OP might like to adapt what I show below AS I have adapted what he
has written.

Option Explicit ' All modules should start so to avoid some bugs


Private Function MIDMsg(ByVal Msg As String, Optional ByVal Config As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function

Public Sub foo()
Dim Msg As String

If True Then
Msg = "You owe a balance due to Virginia at this time!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with an Funds Transfer?"
' Brackets in call statements seem odd to me
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
' Use call if you really want to have brackets
Call Workbooks.Open("C:\Documents and Settings\MyFolder\My Documents\TestII.xlsx")
Else
' Or leave them out for textual economy.
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
' I have corrected a slip.
MIDMsg "No B[a]lance Owed"
End If

End Sub

Thank you


You're welcome. I found your problem entertaining.
N.B. my long lines may wrap in an unfriendly way.
--
Walter Briscoe
Thank you Walter: I will set this up.

Regards

Gene
  #4   Report Post  
Old March 3rd 13, 01:03 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default

Quote:
Originally Posted by Gene Haines View Post
Thank you Walter: I will set this up.

Regards

Gene
Walter: Forgive my lack of VBA experience, but I am not sure where to go from here. I've pasted the code and can't figure out how to get oast the Compile Error: Expected End Sub in the Private WorkBook-Open()

Option Explicit
Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("F2").Value 0 Then

Public Sub foo()
Dim Msg As String

Private Function MIDMsg(ByVal Msg As String, Optional ByVal Config As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function



If True Then
Msg = "You owe a balance due to Virginia at this time!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with a Funds Transfer?"
' Brackets in call statements seem odd to me
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
' Use call if you really want to have brackets
Call Workbooks.Open("C:\Documents and Settings\My Folder\My Documents\TestII.xlsx")
Else
' Or leave them out for textual economy.
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
' I have corrected a slip.
MIDMsg "No Balance Owed"
End If

End Sub
  #5   Report Post  
Old March 3rd 13, 02:11 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 279
Default Msg Box Title

In message of Sun, 3 Mar 2013
13:03:34 in microsoft.public.excel.newusers, Gene Haines <Gene.Haines.b7
writes

Gene Haines;1609945 Wrote:
Thank you Walter: I will set this up.

Regards

Gene


Walter: Forgive my lack of VBA experience, but I am not sure where to


I suggest you lack understanding rather than knowledge.

go
from here. I've pasted the code and can't figure out how to get oast


Pasting was not an appropriate operation.
You needed to merge the ideas in my code with your own.
Why did you throw away the indentation in my code?
It aids understanding.

the
Compile Error: Expected End Sub in the Private WorkBook-Open()


What did you do in response to that error, other than post?


Option Explicit
Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("F2").Value 0 Then

Public Sub foo()
Dim Msg As String

Private Function MIDMsg(ByVal Msg As String, Optional ByVal Config
As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function



If True Then
Msg = "You owe a balance due to Virginia at this time!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with a Funds Transfer?"
' Brackets in call statements seem odd to me
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
' Use call if you really want to have brackets
Call Workbooks.Open("C:\Documents and Settings\My Folder\My
Documents\TestII.xlsx")
Else
' Or leave them out for textual economy.
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
' I have corrected a slip.
MIDMsg "No Balance Owed"
End If

End Sub



You might try a module like this.
Option Explicit

Private Function MIDMsg(ByVal Msg As String, _
Optional ByVal Config As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function

Private Sub Workbook_Open()
Const Msg As String = "You owe a balance to Virginia!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with a Funds Transfer?"
Const fname As String = "C:\Documents and Settings\" & _
"MyFolder\My Documents\TestII.xlsx"

If Worksheets("Sheet1").Range("F2").Value 0 Then
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
Workbooks.Open fname
Else
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
MIDMsg "No Balance Owed"
End If
End Sub

I have thrown away some of your text.
--
Walter Briscoe


  #6   Report Post  
Old March 5th 13, 01:23 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 21
Default

Quote:
Originally Posted by Walter Briscoe View Post
In message of Sun, 3 Mar 2013
13:03:34 in microsoft.public.excel.newusers, Gene Haines <Gene.Haines.b7
writes

Gene Haines;1609945 Wrote:
Thank you Walter: I will set this up.

Regards

Gene


Walter: Forgive my lack of VBA experience, but I am not sure where to


I suggest you lack understanding rather than knowledge.

go
from here. I've pasted the code and can't figure out how to get oast


Pasting was not an appropriate operation.
You needed to merge the ideas in my code with your own.
Why did you throw away the indentation in my code?
It aids understanding.

the
Compile Error: Expected End Sub in the Private WorkBook-Open()


What did you do in response to that error, other than post?


Option Explicit
Private Sub Workbook_Open()
If Worksheets("Sheet1").Range("F2").Value 0 Then

Public Sub foo()
Dim Msg As String

Private Function MIDMsg(ByVal Msg As String, Optional ByVal Config
As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function



If True Then
Msg = "You owe a balance due to Virginia at this time!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with a Funds Transfer?"
' Brackets in call statements seem odd to me
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
' Use call if you really want to have brackets
Call Workbooks.Open("C:\Documents and Settings\My Folder\My
Documents\TestII.xlsx")
Else
' Or leave them out for textual economy.
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
' I have corrected a slip.
MIDMsg "No Balance Owed"
End If

End Sub



You might try a module like this.
Option Explicit

Private Function MIDMsg(ByVal Msg As String, _
Optional ByVal Config As Long = 0) As Long
Const Title As String = "Metals Inventory Database"

MIDMsg = MsgBox(Msg, Config, Title)
End Function

Private Sub Workbook_Open()
Const Msg As String = "You owe a balance to Virginia!" & _
vbLf & vbLf & _
"Would you like to fufill that obligation" & _
" with a Funds Transfer?"
Const fname As String = "C:\Documents and Settings\" & _
"MyFolder\My Documents\TestII.xlsx"

If Worksheets("Sheet1").Range("F2").Value 0 Then
If MIDMsg(Msg, vbYesNo + vbQuestion) = vbYes Then
Workbooks.Open fname
Else
MIDMsg "Then please do a Journal Voucher. Thank you."
End If
Else
MIDMsg "No Balance Owed"
End If
End Sub

I have thrown away some of your text.
--
Walter Briscoe
Walter: I actually tried to solve the problem myself believe it or not. I put in an Else If and then an End Sub and the code stopped, which I guess it should have. I cannot fully understand at this time exactly how the processes flow in VBA Code This is my first attempt in VBA. I am looking for a user friendly VBA book to purchase and teach myself. Thanks for you input.

Regards

Gene


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to hide a chart title, but keep the title in the chart KBratt Charts and Charting in Excel 1 July 16th 09 12:13 AM
Named range=Column title,comumn title in cellB6 use B6in equation Graham Excel Discussion (Misc queries) 2 July 21st 06 10:03 AM
Show full path title in title bar? Nor New Users to Excel 4 November 4th 05 06:00 PM
Pasting Objects into Chart title and Axis title Sam Charts and Charting in Excel 1 June 6th 05 08:50 PM
VB msg box - can it have a title? StargateFan[_3_] Excel Programming 4 February 3rd 05 02:43 AM


All times are GMT +1. The time now is 10:46 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017