200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Can you assist with this Excel file.
In the logfile sheet I want the Message ref number and Subject to be logged when the button is clicked
also on the sheet1, when the button is clicked the Blue cell should display the next sequential value and the Subject cell "D8" should become Blank ( ready for the next subject Input)
Hello Brian and welcome to the forum,
A couple of changes were necessary to achieve what you want. First, on "Sheet1" cell "C4" format had to be changed from Text to Custom – 0000 to keep code simple for incrementing the number by 1. Second, the formula in "M3" had to be changed from =CONCATENATE(C2,"/",C4,"/",D3) to =CONCATENATE(C2,"/",TEXT(C4,"0000"),"/",D3) so the number in "C4" would be displayed correctly. Third, I added conditional formatting to "D8" so it has fill colour of green if it is empty.
There are two ways this can be coded. In the new macros there is a check to make sure the "Subject" cell (D8) is not empty. Used variables for cell contents of A8 and D8. This was needed for A8 because it is a formula, so did it for both to stay consistent. Used a variable for the next blank row on the "Log File" sheet to prevent existing entries from being overwritten. In the second macro variables for A8 and D8 are not needed. All of this is in the attached file.
' new code
Dim strMSG As String ' Sheet1 A8
Dim strSUB As String ' Sheet1 D8
Dim nxtROW As Long ' next blank row on "Log File"
Dim nxtStart As String ' Sheet1 C4
' check if a subject has been entered
If Sheets("Sheet1").Range("D8") = "" Then
MsgBox " Subject missing"
Exit Sub ' subject cell is blank
End If
' increment Start Number
nxtStart = Sheets("Sheet1").Range("C4") + 1
Sheets("Log File").Select
' determine row number after last entry in Col "A"
nxtROW = Range("A" & Rows.Count).End(xlUp).Row + 1
' copy Messsage Number to "Log File" Col "A"
strMSG = Sheets("Sheet1").Range("A8").Value
Sheets("Log File").Cells(nxtROW, 1).Value = strMSG
' copy Subject to "Log File" Col "B"
strSUB = Sheets("Sheet1").Range("D8").Value
Sheets("Log File").Cells(nxtROW, 2).Value = strSUB
' increment Start Number field
Sheets("Sheet1").Range("C4").Value = nxtStart
' clear subject cell
Sheets("Sheet1").Range("D8").ClearContents
Sheets("Sheet1").Range("D8").Select' new code ver 2
' this version is another way to do it
Dim nxtROW As Long ' next blank row on "Log File"
Dim nxtStart As String ' Sheet1 C4
' check if a subject has been entered
If Sheets("Sheet1").Range("D8") = "" Then
MsgBox " Subject missing"
Exit Sub ' subject cell is blank
End If
' increment Start Number
nxtStart = Sheets("Sheet1").Range("C4") + 1
' determine row number after last entry in Col "A"
nxtROW = Sheets("Log File").Range("A" & Rows.Count).End(xlUp).Row + 1
' copy Messsage Number to "Log File" Col "A"
Sheets("Sheet1").Range("A8").copy
Sheets("Log File").Cells(nxtROW, 1).PasteSpecial Paste:=xlPasteValues
' copy Subject to "Log File" Col "B"
Sheets("Sheet1").Range("D8").copy
Sheets("Log File").Cells(nxtROW, 2).PasteSpecial Paste:=xlPasteValues
' increment Start Number field
Sheets("Sheet1").Range("C4").Value = nxtStart
' clear subject cell
Sheets("Sheet1").Range("D8").ClearContents
Sheets("Sheet1").Range("D8").Select
If this solves your problem, please mark my answer as Selected
Cheers :-)