PDA

View Full Version : Help to separate two codes and place one in a Userform Function



gnaske
12-08-2018, 01:13 AM
Hi Forum.

I already had a Time Stamp code in a Project.
VBA code placed in Sheets("Ark1").

Then I made a Test Workbook, uploaded it in another forum and asked if it was possible to make a code, changing between hh:mm and digit with comma in specific cells, using a Userform.

I got help with the code, but the guy combined the code with the Time Stamp code in Sheets("Ark1").
And the two codes somehow doesn't work together, when they are combined into one code in my "real" Workbook.

The gray cells in the attached Test Workbook, Tab Ark1, is the cells which change between hh:mm and digit with comma.

The code guy made is placed in between the Time Stamp code and looks like this and you'll see it in the attached file.

'----------------------------------------------------------------'
'*' code to change between hh:mm and diget with comme - Start '
'----------------------------------------------------------------'
'*' Bemærk... '
'*' - Merged celler skal kun defineres med den første celle '
'*' - Range kan defineres helt normalt - F.eks. "A1:A5, A12:A17" '
'----------------------------------------------------------------'
ElseIf Not Intersect(Target, Range("A1")) Is Nothing Then
Set totalsRng = Range("R13, T13:U13, V13, Y13, Z13, AB13:AD13, AB25:AD25")
'If totalsRng Is Nothing Then
' MsgBox "Somebody changed workbook - no formula found in rows 11 & 24!", vbCritical, "Oooops!"
'Else
If totalsRng Is Nothing Then
Exit Sub
Else
Application.EnableEvents = False
Select Case LCase(Target.Cells(1, 1).Value)
Case "timer og minutter"
For Each totalCell In totalsRng
If Right(totalCell.Formula, 3) = "*24" Then
totalCell.Formula = Left(totalCell.Formula, Len(totalCell.Formula) - 3)
totalCell.NumberFormat = "[hh]:mm"
ElseIf Right(totalCell.Formula, 1) <> ")" Then
problemStr = problemStr & "unexpected formula in Cell: " & totalCell.Address(0, 0) & vbLf
End If
Next totalCell
Case "100 dele"
For Each totalCell In totalsRng
If Right(totalCell.Formula, 1) = ")" Then
totalCell.Formula = totalCell.Formula & "*24"
totalCell.NumberFormat = "0.00"
ElseIf Right(totalCell.Formula, 3) <> "*24" Then
problemStr = problemStr & "unexpected formula in Cell: " & totalCell.Address(0, 0) & vbLf
End If
Next totalCell
Case Else
MsgBox " Det er kun tilladte at vælge" _
& vbNewLine & " - Timer og minutter" _
& vbNewLine & " - 100 dele", vbCritical, ""
UserForm1.ComboBox1 = ""
End Select
Application.EnableEvents = True
End If
End If
'-----------------------------------------------------------'
'*' code to change between hh:mm and diget with comme - End ' '-----------------------------------------------------------'
Is it possible to split up the two codes as following
1) Still keep the Time Stamp code in Sheets("Ark1").. No change needed - I have the code..
2) To make the hh:mm to digit in e.g. a Function in the Userform

It should of course still be possible to define which Sheet("xxx") and Range ("x:x, y:y"), the hh:mm to digit should work on.

I tried to make the code, but can't figure out how..

Thank you in advance

Ib