Question

Help With VBA Coding

User options OK Select one of the following options: Options Cancel Find an optimal production plan C View demand and cost data Enter new demand and cost data C Change smoothing constants New Data Enter the newly observed demand and Add Data unit cost for Feb-2006 Demand Unit cost: Screenshot of "New Data" dialog box New Data Enter the newly observed demand and Add Data unit cost for Feb-2006 Demand: Unit cost 1430 450 Application Sample5-Microsoft Excel Home Insert Page Layout Formulas Data Review View Team X File Calibri 函 42" B 1 프.AA. Number Styles Cells Paste Editing Clipboard Font Alignment N9 55 Apr-05 56 May-05 57 Jun-05 58 Jul-05 59 Aug-05 60 Sep-05 61 Oct-05 62 Nov-05 63 Dec-05 64 Jan-06 65 Feb-06 108.36 109.20 110.92 111.09 112.41 111.68 113.61 114.72 115.09 117.76 120.18 767 108.19 1003111.71 1149108.53 624112.37 1661107.38 1208 115.17 667 114.29 760 113.09 1885120.58 122 450 877.50 942.70 1262.95 1689.99 1609.89 1217.22 721.82 842.77 1412.91 861.84 796.65 920 1430 67 69 70 71 72 73 74 anationData Ready

Answer 1

히× e Wi

1. Add UserForm1

User Option Select one of the following options: Options OK Find an optimal production plarn Cancel C View demand and cost data Enter new demand and cost data C Change smoothing constants

userform1 exported data

VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm1
Caption = "User Option"
ClientHeight = 3585
ClientLeft = 45
ClientTop = 390
ClientWidth = 6420
OleObjectBlob = "UserForm1.frx":0000
StartUpPosition = 1 'CenterOwner
End
Attribute VB_Name = "UserForm1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub CommandButton1_Click()
If (opadd.Value = True) Then
Call AddNewData
End If

  
End Sub

Private Sub AddNewData()
Dim rownum As Long
rownum = Sheet1.Cells(rows.Count, 1).End(xlUp).Row
Module1.currRow = rownum + 1
If (rownum = 1) Then
Module1.currDate = Now
Else
Module1.currDate = Sheet1.Cells(rownum, 1)
End If

UserForm2.lblheading.Caption = "Enter the newly observer demand and cost data of " & MonthName(Month(Module1.currDate)) & "-" & Year(Module1.currDate)
UserForm2.Show
End Sub

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

2. userform2

UserForm2 Enter the newly observer demand and cost data of April-2019 1450 Demand: Add Data 12 Cost:

exported userform2 data

VERSION 5.00
Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} UserForm2
Caption = "UserForm2"
ClientHeight = 2505
ClientLeft = 45
ClientTop = 390
ClientWidth = 4710
OleObjectBlob = "UserForm2.frx":0000
StartUpPosition = 1 'CenterOwner
End
Attribute VB_Name = "UserForm2"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub CommandButton1_Click()

If IsNumeric(tbxdemand.Text) = True And IsNumeric(tbxCost.Text) = True Then
Sheet1.Cells(Module1.currRow, 1) = MonthName(Month(Module1.currDate)) & "-" & Year(Module1.currDate)
Sheet1.Cells(Module1.currRow, 2) = UserForm2.tbxdemand.Text
Sheet1.Cells(Module1.currRow, 3) = UserForm2.tbxCost.Text
UserForm2.Hide
Else
MsgBox ("Enter valid values")
End If
End Sub

3. Module code to open form

Public currDate As Date
Public currRow As Long

Sub ShowUserOptions()
UserForm1.Show

End Sub

scrrenshots

for run macro

Macro Macro name: Run Step Into Edit tions Delete Options Description Cancel

Activate Windows Go to Settings to activate Window

Type a question for help.x 包稳.id △务 A odale End it eo2.1bsheading.Caption-"Encer the nevly observer demand and oors data of Monhne CHonth Olodsles ourrDace)) Import Fle ontrolToTest Activate Windows GO tO Settings to activate Window

히× Type a question for help.x ㄨㄧ Activate Windows o to Settings to activate Window

Type a question for help.x Activate Windows o to Settings to activate Window

히× Type a question for help.x Select one of the following ophen Find an opimal production plan View demand and cost dats Enter new demand and cost data Change smoothing constants Activate Windows o to Settings to activate Window

UserForm2 Enter the newly observer demand and cost data of April-2019 1460 Demand: Add Data 「sol 50 Cost: