Using Spin Button to increase or decrease date in VBA

Private Sub SpinButton1_SpinDown()

Dim datevar As Date
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar - 1)

End Sub

Private Sub SpinButton1_SpinUp()
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar + 1)
End Sub

Automating Print preview of excel reports using vba

'this is for setting the center header of print which will print as Active Employee List
ersheet.PageSetup.CenterHeader = "Active Employee List"
'this is for setting the right footer of print which will print as Page (number of current page) of Total Pages
ersheet.PageSetup.RightFooter = "Page &P of &N"

'this commented out section works best with portrait printing
'ersheet.PageSetup.Zoom = 60
'ersheet.PageSetup.FitToPagesWide = 1

'ersheet.PageSetup.FitToPagesTall = False

'this section works best for landscape printing
With ersheet.PageSetup
'for setting portrait or landscape
.Orientation = xlLandscape
'these next two line will fit all columns in one page
.FitToPagesWide = 1
.FitToPagesTall = 1
'this line is responsible for continuing one fixed row on several print pages ,it is similar as excel freeze pane
.PrintTitleRows = ersheet.Rows(1).Address
End With

ersheet.PrintPreview

Output

Source:

https://docs.softartisans.com/officewriterwindows/3.0.5/ExcelWriterASP/features/headersandfooters.aspx

https://stackoverflow.com/questions/34052790/vba-code-to-set-print-area-fit-to-1x1-page-and-not-set-print-area-for-certain-t

https://www.youtube.com/watch?v=X4QBS94iNdo&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U&index=13

Creating Moving Average chart automatically based on 4 series collection(Moving Averages,UCL,CL,LCL) using Excel VBA ,VBA Teacher Sourav,Kolkata 08910141720

Sub movingrangechartmacro()

Dim co As ChartObject
    Dim ct As Chart
    Dim scl As SeriesCollection
    Dim ser1 As Series
    Dim chartname As String

    Sheets("MovingRange").Select

       Set co = Worksheets("MovingRange").ChartObjects.Add(Range("H8").Left, Range("H8").Top, 2500, 500)
       
       'chartname = Worksheets("process_capability").Range("D15").Value
       chartname = "movingrange"
       co.Name = chartname
      ' MsgBox (co.Name)
       Set ct = co.Chart
       With ct
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Text = "Moving Range Chart"
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("B1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("B1").Offset(1, 0), Worksheets("MovingRange").Range("B1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("D1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("D1").Offset(1, 0), Worksheets("MovingRange").Range("D1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("E1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("E1").Offset(1, 0), Worksheets("MovingRange").Range("E1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("F1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("F1").Offset(1, 0), Worksheets("MovingRange").Range("F1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
      
       .Axes(xlValue).MajorGridlines.Select
         Selection.Delete
         Dim high, low As Long
         
      high = Round(((Application.WorksheetFunction.max(.SeriesCollection(2).Values))), 0)
      'MsgBox (high)
         low = Round(((Application.WorksheetFunction.min(.SeriesCollection(4).Values))), 0)
      'MsgBox (low)
      .Axes(xlValue).Select
      .Axes(xlValue).MinimumScale = low
      .Axes(xlValue).MaximumScale = high
      .Axes(xlValue).MajorUnit = 0.5
      Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown)).Select
    high = Round(Application.WorksheetFunction.max(Selection), 0)
    'MsgBox (high)
     low = Round(Application.WorksheetFunction.min(Selection), 0)
     .Axes(xlCategory).Select
      .Axes(xlCategory).MinimumScale = low
      .Axes(xlCategory).MaximumScale = high
      .Axes(xlCategory).MajorUnit = 1
     
     
     'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day Index"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Gas Use"

'coloring and designing the marker and the data series

Set sc1 = ct.SeriesCollection(1)
With sc1

    .Format.Line.Weight = 2 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 0, 0) 'now it works

 .MarkerSize = 8
 .MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 .MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
    
    
     
       
 'coloring UCL seriescollection
 Set sc1 = ct.SeriesCollection(2)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With

  'coloring LCL seriescollection
 Set sc1 = ct.SeriesCollection(4)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
     
     
  'coloring CL seriescollection
 Set sc1 = ct.SeriesCollection(3)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 255, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With


       
  End With
End Sub


The picture of chart to be created

The source Data

The video where the chart is created manually

https://www.youtube.com/watch?v=uGgd3iiQrUI

The link from stackoverflow which helped me regarding marker on series collection on a chart issue 

https://stackoverflow.com/questions/29675485/excel-vba-line-color-marker-line-color?answertab=votes#tab-top

Add two numbers present on sheet on User Alert using Google Apps Script

function addtwonumbersonsheet() {
 
var sh1=SpreadsheetApp.getActiveSpreadsheet();
var sheet1=sh1.getSheetByName("GAS_Sheet");
var num1=sheet1.getRange(2,1).getValue();
var num2=sheet1.getRange(2,2).getValue();  
var sh=SpreadsheetApp.getUi();
var response=sh.alert("Do you want to add the two number?",sh.ButtonSet.YES_NO);
if (response == sh.Button.YES)
{
  sheet1.getRange(2,3).setValue(num1+num2);
 
}

}

Taking input and Switch Case using Google Apps Script

//Declaring variables
var ui = SpreadsheetApp.getUi();
// Get the value from the user
var get_text= ui.prompt("Enter any number from 1 to 7").getResponseText();
//Convert the data type to integer
var get_num=parseInt(get_text);
//Comparing the entered number with days in a week
switch(get_num)
 {
   
   case 1:
        ui.alert("Today is sunday");
        break;   
   case 2:
        ui.alert("Today is monday");
        break;
   case 3:
        ui.alert("Today is tuesday");
        break;
   case 4:
        ui.alert("Today is Wednesday")
        break;
   case 5:
       ui.alert("Today is Thursday");
        break;
   case 6:
        ui.alert("Today is Friday");
        break;
   case 7:
        ui.alert("Today is Saturday");
        break;
   default:
         ui.alert("Entered number is not fall between 1 to 7")
 }