Command button to insert data onto worksheet

[jlwiii34]

I would like to use a command button to submit data input into a userform to a worksheet depending on what option is selected from a listbox. I have two worksheets one named "Linux" the other "Microsoft". When an OS is selected related to one or the other the data input in the userform will be inserted into the appropriate worksheet upon clicking on command button.


Currently used code:
Private Sub cmdAddNewHostMS_Click()

Dim lrow As Long
Dim wb As Workbook
Dim wsl As Worksheet
Dim wsm As Worksheet
Set wb = ThisWorkbook
Set wsm = Worksheets("Microsoft")
Set wsl = Worksheets("Linux")

'find empty row on Microsoft worksheet
lrow = wsm.Range("A1").CurrentRegion.Rows.Count
With wsm.Range("A1")

'insert data into worksheet
.Offset(lrow, 2) = Me.txtServerFunction.Value
.Offset(lrow, 14) = Me.txtDataCenter.Value
.Offset(lrow, 5) = Me.lstOperatingSystem.Value
.Offset(lrow, 11) = Me.txtAppsInstalled.Value
.Offset(lrow, 6) = Me.txtVirtualRam.Value * 1.5
.Offset(lrow, 19) = Me.txtVirtualCPU.Value
.Offset(lrow, 20) = Me.txtVirtualRam.Value
.Offset(lrow, 23) = Me.txtOSVolume.Value
.Offset(lrow, 27) = Me.txtBronzeTier.Value
.Offset(lrow, 31) = Me.txtSilverTier.Value
.Offset(lrow, 35) = Me.txtLogsTier.Value
.Offset(lrow, 39) = Me.txtGoldTier.Value
.Offset(lrow, 21) = Format(CDbl(txtOSVolume.Value) + CDbl(txtBronzeTier.Value) + CDbl(txtSilverTier.Value) + CDbl(txtLogsTier.Value) + CDbl(txtGoldTier.Value))

End With

Unload Me
frmSrvInfo.Show

End Sub

[HaHoBe]

Hi, jlwiii34,

please use code-tags when displaying your code here for the ease of readability.

Maybe give this code a try:

Please Login or Register  to view this content.

Ciao,
Holger

[jlwiii34]

Mostly works the way I am looking for (did not include code for adding data to linux sheet). my issue now is only copying data entered for Microsoft to "Microsoft" sheet and linux data to "Linux" sheet. The following code should input the data to the Linux work sheet leaving the Microsoft data blank and worksheet alone.

Please Login or Register  to view this content.

[jlwiii34]

Ok, using the following code I am trying to send data input on a userform to a particular worksheet based on what listbox selection is made. I am getting an error saying the "Object doesn't support this property or method" on the code "If ws = "Microsoft" Then". Is there any suggestion on how to fix this error?

Please Login or Register  to view this content.

[HaHoBe]

Hi, jlwiii34,

ws is set to show a worksheet, and that worksheet will most definitely have a name to which you may refer like

Please Login or Register  to view this content.

And maybe you could use InStr to check whether Windows Server or Linux are part of the selection to set a worksheet.

Ciao,
Holger