If you save the "blank" as a template file (.XLTX) or a macro enabled template file (.XLTM) then hitting the Save option (or even the Ctrl+S key combo) will force the user to have to "Save As" by default.
Posts by gijsmo
-
-
Maybe change the FOR loop - you could check for a "Y" value in col Q (or modify the code to see if it's not an "N" value)
From:
CodeFor Each cell In myDataRng If Trim(sEmail_ids) = "" Then sEmail_ids = cell.Offset(0, 0).Value ' keep the Offset (0, 0) Else sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value End If Next cellTo:
CodeFor Each cell In myDataRng If UCase(cell.Offset(0, 2)) = "Y" Then 'check if there is a "Y" in column Q If Trim(sEmail_ids) = "" Then sEmail_ids = cell.Offset(0, 0).Value ' keep the Offset (0, 0) Else sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value End If End If Next cell -
You are welcome, again
-
Maybe try this instead:
Code
Display MorePrivate Sub cmdBestellen_Click() Dim r As Long, j As Long j = Sheets("Bestelde Artikelen").Range("M2").Value + 1 For r = 1 To Me.lstboxBesteldeArt.ListCount - 1 ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.lstboxBesteldeArt.List(r, 0) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.lstboxBesteldeArt.List(r, 1) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.lstboxBesteldeArt.List(r, 2) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.lstboxBesteldeArt.List(r, 3) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.lstboxBesteldeArt.List(r, 4) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.lstboxBesteldeArt.List(r, 5) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.lstboxBesteldeArt.List(r, 6) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.lstboxBesteldeArt.List(r, 7) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 8) = j Next r Call M_ClearAll2.ClearAll2 Unload Me End Sub -
You're welcome
-
In that case, maybe need to change
From:
CodeThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1To:
CodeThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(2, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1 -
Maybe try this:
Code
Display MorePrivate Sub cmdBestellen_Click() Dim r As Long ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1 For r = 1 To Me.lstboxBesteldeArt.ListCount - 1 ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Me.lstboxBesteldeArt.List(r, 0) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 1) = Me.lstboxBesteldeArt.List(r, 1) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 2) = Me.lstboxBesteldeArt.List(r, 2) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 3) = Me.lstboxBesteldeArt.List(r, 3) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 4) = Me.lstboxBesteldeArt.List(r, 4) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 5) = Me.lstboxBesteldeArt.List(r, 5) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 6) = Me.lstboxBesteldeArt.List(r, 6) ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(0, 7) = Me.lstboxBesteldeArt.List(r, 7) Next r Call M_ClearAll2.ClearAll2 Unload Me End Sub -
You're welcome
-
Assuming Red is the value you want to display if the condition for Green & Amber are not met, this might be all you need:
=IF(Q5>Data!L10,"Green",IF(ABS(Q5-Data!$L$10)<=0.5,"Amber", "Red"))
-
You're welcome!
-
Maybe try this:
Code
Display MoreSub ConvertTextToNumber() Dim Rng As Range Dim Title As String 'set Rng to nothing initially to trap Cancel out of Msgbox Set Rng = Nothing Title = "MSG Box" 'error handling if Msgbox is cancelled On Error Resume Next Set Rng = Application.InputBox("Input Range", Title, Type:=8) If Not Err.Number Then Rng.Value = Rng.Value Else Err.Clear On Error GoTo 0 End If End Sub -
You are most welcome.
-
No problem, this is easily fixed, just need to change the way the lTo value is calculated when it comes to end of year.
Revised version of code is attached.
-
This is clearly an extension of the original request.
May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.
-
Roy is, as always, correct !
AutoFilter is a much faster way to do this type of copying between sheets.
If you want to use that instead of looping, revised version using AutoFilter is attached.
-
The loop in the NewCopy macro that has the "Like" statements in it is created as a series of "ElseIf"s.
This means that, for example, a Call Month value of "December / April / August" will only copy to April because April gets tested before August or December.
If you want to copy data for a Call Month value of "December / April / August" to each of the December, April & August sheets then the loop needs to be redesigned. The attached version does this. Bear in mind that this loop now takes a longer to run and it has to test each row 12 times for each month, not skip to the next row like it did before when one of the ElseIf conditions was met.
-
There's a bunch of ways to do this. One way is in the attached revised copy of your original workbook.
I made a few other changes as well to streamline the code a bit.
I also added a Clear Data button to the Main sheet if you want the option to do this in one go - as the name implies this will clear the data from all the "Month" sheets (except the header row).
-
The codeword is "Like", you still have "=" in the code
CodeIf c Like "*January*" ThenNOT
CodeIf c = "*January*" Then -
Maybe instead of
CodeIf c = "January" Thentry
CodeIf c Like "*January*" Thenand likewise for other months
-
Thanks !
You're welcome.