dual textbox actions

**TonyforVBA** · 09-10-2010, 10:53 AM

Im useing a piece of code (below) which take the mid values of a string from one text box and generates it into differant Month format (09_Sep).
On a seperate tab on the same user form I have a second text box which will have differant values entered into it.
Is there anyway that I can use the below pice of code for both txt boxes?

Strings 'ThisWeekDate' & 'LendingMonth' are defined earlier in the code.
Thanks


'''''''''''''''''''''
'''Re-Format Month'''
'''''''''''''''''''''
Sub ThisMonth()
test = Mid(ThisWeekDate, 4, 2)
or test = LendingMonth<------------------------- Attemp

    Select Case Mid(ThisWeekDate, 4, 2)
        Case "01":      GblMonth = "01_Jan"
        Case "02":      GblMonth = "02_Feb"
        Case "03":      GblMonth = "03_Mar"
        Case "04":      GblMonth = "04_Apr"
        Case "05":      GblMonth = "05_May"
        Case "06":      GblMonth = "06_Jun"
        Case "07":      GblMonth = "07_Jul"
        Case "08":      GblMonth = "08_Aug"
        Case "09":      GblMonth = "09_Sep"
        Case "10":      GblMonth = "10_Oct"
        Case "11":      GblMonth = "11_Nov"
        Case "12":      GblMonth = "12_Dec"
    End Select
End Sub

**Andy Pope** · 09-10-2010, 11:01 AM

create a function rather than a subroutine.
The function takes an argument and returns a value

Sub Test()
    
    GblMonth = ThisMonth(ThisWeekDate)
    
    GblMonth = ThisMonth(LendingMonth)
    
    
End Sub

'''''''''''''''''''''
'''Re-Format Month'''
'''''''''''''''''''''
Function ThisMonth(Arg)

    Dim ReturnValue
    
    Select Case Mid(Arg, 4, 2)
        Case "01":      ReturnValue = "01_Jan"
        Case "02":      ReturnValue = "02_Feb"
        Case "03":      ReturnValue = "03_Mar"
        Case "04":      ReturnValue = "04_Apr"
        Case "05":      ReturnValue = "05_May"
        Case "06":      ReturnValue = "06_Jun"
        Case "07":      ReturnValue = "07_Jul"
        Case "08":      ReturnValue = "08_Aug"
        Case "09":      ReturnValue = "09_Sep"
        Case "10":      ReturnValue = "10_Oct"
        Case "11":      ReturnValue = "11_Nov"
        Case "12":      ReturnValue = "12_Dec"
    End Select
    
    ThisMonth = ReturnValue
    
End Function

**snb** · 09-10-2010, 11:07 AM

Function ThisMonth(c01)
  ThisMonth=format(mid(c01,4,2),"00_") & format(dateserial(2010,val(mid(c01,4,2)),1),"mm")
End Sub

or

Function ThisMonth(c01)
  ThisMonth=format(mid(c01,4,2),"00_") & monthname(mid(c01,4,2))
End Sub

**TonyforVBA** · 09-10-2010, 11:13 AM

Hey Andy,
The problum is that the strings are in differant Date Formats.
Thisweek date = dd-mm-yyyy
LendingMonth = mm
Any ideas??

**Andy Pope** · 09-10-2010, 11:15 AM

Where in your original post did it say that?

**TonyforVBA** · 09-10-2010, 11:20 AM

Hey Andy
"a second text box which will have differant values entered into it" - Apologies, I see how that can be misunderstood as a differant date (in the same format). I should have made that clearer.

**Andy Pope** · 09-10-2010, 11:25 AM

Function ThisMonth(Arg)
if len(Arg) =2 then
    ThisMonth = format(Datevalue("01-" & Arg & "-2010"),"mm_mmm")
else
   ThisMonth = format(datevalue(Arg),"mm_mmm") 
End if
End Function