Posts by gijsmo

gijsmo · Oct 23rd 2022

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.

gijsmo · Sep 4th 2022

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:

Code

    For 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 cell

To:

Code

    For 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

gijsmo · Aug 31st 2022

You are welcome, again

gijsmo · Aug 31st 2022

Maybe try this instead:

Code

Private 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

Display More

gijsmo · Aug 30th 2022

You're welcome

gijsmo · Aug 30th 2022

In that case, maybe need to change

From:

Code

ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1

To:

Code

ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(2, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1

gijsmo · Aug 30th 2022

Maybe try this:

Code

Private 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

Display More

gijsmo · Aug 9th 2022

You're welcome

gijsmo · Aug 7th 2022

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"))

gijsmo · Jul 30th 2022

You're welcome!

gijsmo · Jul 29th 2022

Maybe try this:

Code

Sub 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

Display More

gijsmo · Jun 13th 2022

You are most welcome.

gijsmo · Jun 7th 2022

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.

ExportToCSV v3.xlsm

gijsmo · Apr 25th 2022

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.

gijsmo · Apr 24th 2022

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.

Customer Call Plan 18-4-22 v4.xlsm

gijsmo · Apr 24th 2022

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.

Customer Call Plan 18-4-22 v3.xlsm

gijsmo · Apr 24th 2022

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).

Customer Call Plan 18-4-22 v2.xlsm

gijsmo · Apr 23rd 2022

The codeword is "Like", you still have "=" in the code

Code

If c Like "*January*" Then

NOT

Code

If c = "*January*" Then

gijsmo · Apr 23rd 2022

Maybe instead of

Code

If c = "January" Then

try

Code

If c Like "*January*" Then

and likewise for other months

gijsmo · Apr 17th 2022

Thanks !

You're welcome.

Posts by gijsmo

2 Commands in same Macro in "ThisWorkBook"?

Command Button sends Email to List - with extra flag

Can't get a number in the correct cell

Can't get a number in the correct cell

Can't get a number in the correct cell

Can't get a number in the correct cell

Can't get a number in the correct cell

IF Functions and tolerances.

IF Functions and tolerances.

MSG Box to define range of Convert to Number

MSG Box to define range of Convert to Number

VBA to select rows that contain a selected month?

VBA to select rows that contain a selected month?

VB formula help

VB formula help

VB formula help

VB formula help

VB formula help

VB formula help

Work book crash when deleting a value