• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Inputbox common for three macro subroutines

W

webmax

Member
Dear Team,

I have the below macro code in that i have three subroutine macros ie. balance, status and refund. For selecting the rows where the data available now i use the variable and given the inputbox for resize code. With foundcell.Offset(1).Resize(xx)

My requirements is if i run the below macro the input box is appearing three times. So i have to type input box 3 times. I need a solution that if i run the macro the input box should appear only once.

Code: 
Sub Autorun()
Call balance
Call status
call refund
End Sub

Sub balance()
    Dim foundcell As Range
    Dim xx As Variant
    xx = InputBox("Enter the Count")
    ThisWorkbook.Worksheets("Dash board").Activate
        Set foundcell = ActiveSheet.Cells.Find("Current Balance", , xlValues, xlWhole, xlByRows, xlNext, False)
    If foundcell Is Nothing Then
       MsgBox "This does not appear to be the correct worksheet ; missing the header labelled BALANCE !", vbExclamation
    Else
       With foundcell.Offset(1).Resize(xx)
            .ClearContents
'           =VLOOKUP(B2,'[Template Sample (1).xlsx]Balance'!$A$1:$B$5,2,0)
            .Formula = "=VLOOKUP(B2,'Thisworkbook.path/[User List.xlsx]Sheet1'!$A$1:$G$1365,7,0)" 'Change the name of the workbook to suit
       End With
    End If
  End Sub

Sub status()
    Dim foundcell As Range
      Dim xx As Variant
    xx = InputBox("Enter the Count")
    ThisWorkbook.Worksheets("Dash board").Activate
  
    Set foundcell = ActiveSheet.Cells.Find("Success", , xlValues, xlWhole, xlByRows, xlNext, False)
    If foundcell Is Nothing Then
       MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
    Else
       With foundcell.Offset(1).Resize(xx)
            .ClearContents
'           =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
            .Formula = "=SUMIFS('[Recharge Report.xlsx]Sheet1'!$E$2:$E$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
       End With
    End If
End Sub


Sub refund()
    Dim foundcell As Range
      Dim xx As Variant
    xx = InputBox("Enter the Count")
    ThisWorkbook.Worksheets("Dash board").Activate
  
    Set foundcell = ActiveSheet.Cells.Find("Refund", , xlValues, xlWhole, xlByRows, xlNext, False)
    If foundcell Is Nothing Then
       MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
    Else
       With foundcell.Offset(1).Resize(xx)
            .ClearContents
'           =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
            .Formula = "=SUMIFS('[Recharge Report.xlsx]Sheet1'!$F$2:$F$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
       End With
    End If
End Sub

Sub rechargedebit()
    Dim foundcell As Range
      Dim xx As Variant
    xx = InputBox("Enter the Count")
    ThisWorkbook.Worksheets("Dash board").Activate
  
    Set foundcell = ActiveSheet.Cells.Find("Debit Com", , xlValues, xlWhole, xlByRows, xlNext, False)
    If foundcell Is Nothing Then
       MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
    Else
       With foundcell.Offset(1).Resize(xx)
            .ClearContents
'           =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
            .Formula = "=-SUMIFS('[Recharge Report.xlsx]Sheet1'!$G$2:$G$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
       End With
    End If
End Sub
 
Last edited by a moderator:
simply declare the variable xx outside the subroutines
Then use an input box once in the Autorun sub

Code: 
Public xx As Variant

Sub Autorun()

xx = InputBox("Enter the Count")
Call balance
Call status
Call refund
End Sub

Sub balance()
  Dim foundcell As Range

  ThisWorkbook.Worksheets("Dash board").Activate
  Set foundcell = ActiveSheet.Cells.Find("Current Balance", , xlValues, xlWhole, xlByRows, xlNext, False)
  If foundcell Is Nothing Then
  MsgBox "This does not appear to be the correct worksheet ; missing the header labelled BALANCE !", vbExclamation
  Else
  With foundcell.Offset(1).Resize(xx)
  .ClearContents
'  =VLOOKUP(B2,'[Template Sample (1).xlsx]Balance'!$A$1:$B$5,2,0)
  .Formula = "=VLOOKUP(B2,'Thisworkbook.path/[User List.xlsx]Sheet1'!$A$1:$G$1365,7,0)" 'Change the name of the workbook to suit
  End With
  End If
  End Sub

Sub status()
  Dim foundcell As Range

  ThisWorkbook.Worksheets("Dash board").Activate
  Set foundcell = ActiveSheet.Cells.Find("Success", , xlValues, xlWhole, xlByRows, xlNext, False)
  If foundcell Is Nothing Then
  MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
  Else
  With foundcell.Offset(1).Resize(xx)
  .ClearContents
'  =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
  .Formula = "=SUMIFS('[Recharge Report.xlsx]Sheet1'!$E$2:$E$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
  End With
  End If
End Sub


Sub refund()
  Dim foundcell As Range

  ThisWorkbook.Worksheets("Dash board").Activate
  Set foundcell = ActiveSheet.Cells.Find("Refund", , xlValues, xlWhole, xlByRows, xlNext, False)
  If foundcell Is Nothing Then
  MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
  Else
  With foundcell.Offset(1).Resize(xx)
  .ClearContents
'  =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
  .Formula = "=SUMIFS('[Recharge Report.xlsx]Sheet1'!$F$2:$F$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
  End With
  End If
End Sub

Sub rechargedebit()
  Dim foundcell As Range

  ThisWorkbook.Worksheets("Dash board").Activate
  Set foundcell = ActiveSheet.Cells.Find("Debit Com", , xlValues, xlWhole, xlByRows, xlNext, False)
  If foundcell Is Nothing Then
  MsgBox "This does not appear to be the correct worksheet ; missing the header labelled SUCCESS !", vbExclamation
  Else
  With foundcell.Offset(1).Resize(xx)
  .ClearContents
'  =SUMIFS('[Template Sample (1).xlsx]Daily Report'!B$2:B$11,'[Template Sample (1).xlsx]Daily Report'!A$2:A$11,B2,'[Template Sample (1).xlsx]Daily Report'!C$2:C$11,'Dash board'!F$1)
  .Formula = "=-SUMIFS('[Recharge Report.xlsx]Sheet1'!$G$2:$G$19000,'[Recharge Report.xlsx]Sheet1'!B$2:B$19000,B2,'[Recharge Report.xlsx]Sheet1'!K$2:K$19000,F$1)" 'Change the name of the workbook to suit
  End With
  End If
End Sub
 
You must log in or register to reply here.
Back
Top