PDA

View Full Version : [SOLVED] How to select just letters in the alert and also make a string



elmnas
03-03-2015, 09:15 AM
Hi guys



NewWBName = ActiveWorkbook.Name


MsgBox NewWBName


that generates this result


http://i.imgur.com/Xds4ARI.png


How do I just select

"_no"

and make a string?


COuld someone help me ?

Thank you in advance

Blade Hunter
03-03-2015, 02:08 PM
This should work:



Sub elmnas()
Dim MyString As String 'Dimension as a string
MyString = ActiveWorkbook.Name 'Set the String = workbook name
MyString = Mid(MyString, InStr(1, MyString, "_")) ' Remove part of string up to _
MyString = Replace(MyString, ".xls", "") 'Remove .xls
MsgBox MyString 'Display string
End Sub


If you are comfortable with what it is doing you can put both the string manipulation lines on one line like this:



MyString = Replace(Mid(MyString, InStr(1, MyString, "_")), ".xls", "")


And then if you are comfortable with that you can combine the initial MyString line like this:



MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")


the entire routine then gets down to this:



Sub elmnas()
Dim MyString As String
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
MsgBox MyString
End Sub

elmnas
03-04-2015, 03:51 AM
This should work:



Sub elmnas()
Dim MyString As String 'Dimension as a string
MyString = ActiveWorkbook.Name 'Set the String = workbook name
MyString = Mid(MyString, InStr(1, MyString, "_")) ' Remove part of string up to _
MyString = Replace(MyString, ".xls", "") 'Remove .xls
MsgBox MyString 'Display string
End Sub


If you are comfortable with what it is doing you can put both the string manipulation lines on one line like this:



MyString = Replace(Mid(MyString, InStr(1, MyString, "_")), ".xls", "")


And then if you are comfortable with that you can combine the initial MyString line like this:



MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")


the entire routine then gets down to this:



Sub elmnas()
Dim MyString As String
MyString = Replace(Mid(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, "_")), ".xls", "")
MsgBox MyString
End Sub



I dont make it work why?

I get this error

12961

Blade Hunter
03-04-2015, 02:29 PM
There could be a bunch of reasons, most likely the name doesn't have an underscore in it. What is the name of the sheet you are trying to use?

elmnas
03-06-2015, 08:21 AM
Thank you solved :)