Warning message if it is left "X" cells left

ColdSpirit

New Member
Joined
Sep 30, 2022
Messages
18
Office Version
  1. 2010
Platform
  1. Windows
Hello eveyone!
I have this sheet:
TESTE.xlsm
ABCDEFGHIJK
1NI :0806100USADAS
2NF :0806150
3TOTAL AWB51
4SEQUÊNCIACHECK DIGITSEQ + DIGITAWBColuna1AWBAWB2
5080610018061 1001047 - 8061 10018061001
6080610128061 1012047 - 8061 10128061012
7080610238061 1023047 - 8061 10238061023
8080610348061 1034047 - 8061 10348061034
9080610458061 1045047 - 8061 10458061045
10080610568061 1056047 - 8061 10568061056
047
Cell Formulas
RangeFormula
D3D3=D2-D1+1
C5:C10C5=IF(B5="","",MOD(B5,7))
D5:D10D5=CONCAT(LEFT(G5,4)&" "&RIGHT(G5,4))
E5:E10E5="047 - " &D5
G5,G7:G10G5=IFS(B$5="","",MOD(B$5,7),IF(B$5="","",CONCAT(B5,C5)))
G6G6=IFS(B$5="","",MOD(B$5,7),IF(B$5="","",CONCAT(B6,C6)))
Press CTRL+SHIFT+ENTER to enter array formulas.


And i have this VBA macro assigned to one of the cmdbutton:
VBA Code:
Private Sub CommandButton1_Click()
    Dim ni As Range 'determinar o numero inicial
    Dim nf As Range 'determinar o numero final
    Dim ws As Worksheet 'determinar o valor "ws" como worksheet
    Dim i As Integer
    
    Set ws = ThisWorkbook.Sheets("047") 'associar "ws" à folha "047"
    Set ni = Range("D1") 'associar ni = Célula J1
    Set nf = Range("D2") 'associar fi = Célula J2
    
    
    Range("B5:B156").Columns(1).ClearContents 'limpar a coluna A(1)
    Range("J5:J156").Columns(1).ClearContents 'limpar a coluna H(1)
    
    
    ni = _
    InputBox(Prompt:="Inserir Numero Inicial") 'ni -inserção da numeração inicial (+ criação da inputbox)
    
    nf = _
    InputBox(Prompt:="Inserir Numero Final") 'fi -inserção da numeração final (+ criação da inputbox)
    
    
    With ws.Range("B5") 'range B5 = começo da célula
        .value = ws.Range("D1") '
        .Resize(ws.Range("D3") + 1).DataSeries '
    End With
    
    If ws.Range("c3") > 0 Then
        MsgBox "Numeração AWB inserida com sucesso" 'mensagem após inserção de numeração da awb
    ElseIf ws.Range("c3") <= 0 Then
        MsgBox "Falha na inserção da Numeração AWB. Por favor inserir numero válido" 'mensagem após inserção de numeração da awb
    End If
    
End Sub

Private Sub Nova_awb_Click()
    Dim lngRowCurrent As Long
    Dim strCopyAddress As String, strPasteAddress As String
    
    answer = MsgBox("Quer emitir uma nova AWB?", vbYesNo)
    
    If answer = vbYes Then
        
        With ThisWorkbook.Sheets("047")
            lngRowCurrent = .Range("J" & Rows.Count).End(xlUp).Row + 1
            
            strCopyAddress = "E" & lngRowCurrent & ":E" & lngRowCurrent
            strPasteAddress = "J" & lngRowCurrent & ":J" & lngRowCurrent
            
            .Range(strPasteAddress).Value2 = .Range(strCopyAddress).Value2 'execute copy-paste
            MsgBox ("AWB emitida com sucesso")
        End With
    ElseIf answer = vbNo Then
        MsgBox ("Cancelado")
    Else
        
        Exit Sub
    End If
    
End Sub

And i need 3 warnings for when there is left 5 / 3 and 1 position based on this condition on column "J":
1669369016245.png


Resuming:
Right now i have this sheet for when i click on "Nova AWB" the data on "E" will be copied to "J"!
I need 3 warning based on a condition to warn when there is left 5 / 3 / 1 number.

Is possible? How can i implement this based on the code above?

Many thanks in advance.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top