Posts by chirayuw

Here's some example code. Assumes UID is in column A. To paste into sheet is simple as you'll just do something like Range("A" & LstRow +1) = NVal

Just FYI you might be better of changing those Variables to Public so the values can be used by your other buttons in the UserForm. So Dim changes to Public & the Variables will be outside the button code i.e Public.... then Userform_Initialize

Dim LstRow As Long 'Figure out Last Value in Range
Dim OVal As String 'Old Value
Dim NVal As String 'New Value


With Sheet1
    If .Range("A2") = "" Then
        .Range("A2") = "AAFL0001"
    Else
        LstRow = .Cells(Rows.Count, "A").End(xlUp).Row
        OVal = .Range("A" & LstRow)
        NVal = "AAFL" & Format(Right(OVal, 4) + 1, "0000")
        Me.TextBox1.Value = NVal
    End If
End With

any specific reason? like there are also images/ graphs on the page u don't want to delete?

Sample file attached

try changing ThisWorkbook to ActiveWorkbook

Refer to below link

https://www.mrexcel.com/forum/…te-all-objects-sheet.html

I couldn't quite figure out what u were trying to do but im assuming 1st code only adds 1 row, second code works after the blank row & puts 2 rows between diff years? have a look

Sub test()


Dim LR As Long
Dim i As Long
Dim R As Long
Dim x As Long




LR = Range("C" & Rows.Count).End(xlUp).Row
For i = 9 To LR
    If Range("C" & i).Value > ([A7] + 31) Then
        Rows(i).Resize(1).Insert
        Exit For
    End If
Next i




LR = Range("C" & Rows.Count).End(xlUp).Row
x = 0
For R = i + 1 To LR
    If Year(Cells(R, 3)) <> Year(Cells(R + 1, 3)) Then x = x + 2
Next R
LR = LR + (x - 2)


For R = i + 1 To LR
    If Year(Cells(R, 3)) <> Year(Cells(R + 1, 3).Value) Then
        Rows(R + 1).Resize(2).Insert
        R = R + 3
    End If
Next R


End Sub

Try this. I used website link below to figure out how to do it

http://www.globaliconnect.com/excel/...=79&Itemid=475

Dim StartDate As String
Dim EndDate As String
Dim PT As PivotTable
Dim PFrng As Range


StartDate = CLng(Range("M1"))
EndDate = CLng(Range("N1"))
Set PT = ActiveSheet.PivotTables("PivotTable3")
Set PFrng = PT.PivotFields("POD/ETA").DataRange


PFrng.Cells(1).Group Start:=StartDate, End:=EndDate, By:=1, Periods:=Array(False, False, False, False, True, False, True)

code and sample file please

code & sample file please

There's no such thing as a GUI interface within Excel but you can create a UserForm to mimic functionality you need. I've provided some example links below but you can always Google the rest

http://www.excel-easy.com/vba/userform.html
https://powerspreadsheets.com/create-excel-userforms/
http://www.contextures.com/xlUserForm01.html

I've used Column G as Test column
Cell G2 Formula =$D2
Cell G3 Onwards =IF(SUM($G2,$D3)>=$F3,"Reset",IF($G2="Reset",$D2+$D3,$G2+$D3))

Why not just use a PivotTable. Sample attached. Link below on how to create a pivottable

[ATTACH]n1201716[/ATTACH]

https://support.office.com/en-…e9-40a9-a8e9-f99134456576

see attached

[ATTACH]n1201713[/ATTACH]

Refer to below link for email.
https://www.rondebruin.nl/win/s1/outlook/mail.htm

As for criteria based send you'll need IF to check the cell value & then run the email code if value matches. Refer to second link for IF details
https://excelmacromastery.com/vba-if/

Examples:
https://stackoverflow.com/ques…d-email-based-on-criteria
https://stackoverflow.com/ques…el-depending-on-condition

Looks like Pike beat me to it. Although his is a more complete solution. My offering was the tutorial link below.

http://www.thesmallman.com/blo…-non-dependent-comboboxes

depends on whether the first visible sheet is always selected in the files. if it is then u don't need it. if it isn't then you need to select it first.

secondly that code doesn't work cos u gotta use activeworkbook not workbook.

another thing to watch out for is the Worksheets(2) does not necessarily mean the 2nd sheet in the file, cos technically you can rename any sheet to whatever u want & move em around in the file but 2 will refer to what it shows as 2nd sheet in VBA module. so technically any sheet in the file could be sheet 2 in vba module

so to select first visible sheet - refer to below thread, 3rd post

https://www.mrexcel.com/forum/…rmine-1st-visible-ws.html

Is division files the folder name or the file name? if its the folder name then add a \ at the end of folderpath

You're missing an End With. To be honest I'm surprised your code didn't give you a debug error. Is your page setup for current sheet & not worksheet(2)? cos your code seems to look that way. If t is for worksheet2 then change "Activesheet.PageSetup" to ".PageSetup"

'Define Variables
Dim FolderPath As String
Dim FileName As String


'Set Variables
FolderPath = "C:\Users\jjones\Desktop\Macros\Testing\Division Files"
FileName = Dir(FolderPath & "*.xls*")


'Loop Start
Do While FileName <> ""


Workbooks.Open (FolderPath & FileName), UpdateLinks:=0


If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
With Workbook.Worksheets(2)
    Range("E19").Select
    ActiveWindow.FreezePanes = True
    Rows("18:18").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$18:$18"
        .PrintTitleColumns = ""
    End With
End With
   
    Workbooks(FileName).Close SaveChanges:=True
    FileName = Dir()


Loop

I Haven't written the entire code for it but this should explain how to paste the details against the same PRF number. Add this to the Add/Edit button code.

First 2 rows of the code figure out which row has the matching PRF number. The third row is pasting the Case Number.

Just copy paste & modify the third row for the rest of the pasting by changing the column "C" to whichever column you need to paste in, and the "Txtcasenumber" to whatever field you need pasting from your form

Dim PRFrow As String


PRFrow = Range("D:D").Cells.Find(what:=Me.cboprfnumber.Text, LookIn:=xlValues, lookat:=xlWhole).Row


Range("C" & PRFrow) = Me.Txtcasenumber

try this

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("A7") = 9 And Range("F5") > 720 Then
    MsgBox "Maximum H/S Size Limit for R9 = 720mm", vbCritical, ""
ElseIf Range("A7") = 11 And Range("F5") > 780 Then
    MsgBox "Maximum H/S Size Limit for R11 = 780mm", vbCritical, ""
End If


End Sub