Form Code

Onicat

New Member
Joined
Feb 16, 2019
Messages
11
Good Day,
I have been working on this vb code based off some of your code, for building fillible forms. We are having an issue with the Private Sub SaveClient_Click Subroutine. The reason I changed the cell numbers, is to move them to the starting location on our workbook. as we want to have the data add a new row starting at C9, and just adding new clients as we get them.


Any help would be great.
Thanks - Lost Coder


Private Sub SaveClient_Click()
'Copy input values to sheet.
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(9, 3).Value = Me.SDSID.Value
.Cells(9, 4).Value = Me.LastName.Value
.Cells(9, 5).Value = Me.FirstName.Value
.Cells(9, 6).Value = Me.Harmony.Value
.Cells(9, 7).Value = Me.StreetAddress.Value
.Cells(9, 8).Value = Me.City.Value
.Cells(9, 9).Value = Me.State.Value
.Cells(9, 10).Value = Me.Zip.Value
.Cells(9, 11).Value = Me.DOB.Value
.Cells(9, 12).Value = Me.Gender.Value
.Cells(9, 13).Value = Me.Mediciad.Value
.Cells(9, 14).Value = Me.BillingStatus.Value
End With



End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
What's the issue...
Right now it's not adding the new client data to the worksheet when I click the "Add Button". Above is the script I have attached to the button.

When I run it, nothing happens. No cell's get populated, and no error comes up. All the other buttons work just fine.

I'm trying to figure out why it's not populating the cells as it should be :)
 
Upvote 0
Why don't you add this line to the top of the code to make sure that it is running...

Code:
MsgBox "Running"

Step through using F8 and see what is happening.
 
Upvote 0
Is it not populating the cells, or is it simply overwriting them each time?
If the latter try
Code:
Private Sub SaveClient_Click()
   'Copy input values to sheet.
   Dim ws As Worksheet
   Set ws = Worksheets("Sheet1")
   With ws
      .Rows(9).Insert
      .Cells(9, 3).Value = Me.SDSID.Value
      .Cells(9, 4).Value = Me.LastName.Value
      .Cells(9, 5).Value = Me.FirstName.Value
      .Cells(9, 6).Value = Me.Harmony.Value
      .Cells(9, 7).Value = Me.StreetAddress.Value
      .Cells(9, 8).Value = Me.City.Value
      .Cells(9, 9).Value = Me.State.Value
      .Cells(9, 10).Value = Me.Zip.Value
      .Cells(9, 11).Value = Me.DOB.Value
      .Cells(9, 12).Value = Me.Gender.Value
      .Cells(9, 13).Value = Me.Mediciad.Value
      .Cells(9, 14).Value = Me.BillingStatus.Value
   End With
End Sub
 
Upvote 0
Is it not populating the cells, or is it simply overwriting them each time?
If the latter try
Code:
Private Sub SaveClient_Click()
   'Copy input values to sheet.
   Dim ws As Worksheet
   Set ws = Worksheets("Sheet1")
   With ws
      .Rows(9).Insert
      .Cells(9, 3).Value = Me.SDSID.Value
      .Cells(9, 4).Value = Me.LastName.Value
      .Cells(9, 5).Value = Me.FirstName.Value
      .Cells(9, 6).Value = Me.Harmony.Value
      .Cells(9, 7).Value = Me.StreetAddress.Value
      .Cells(9, 8).Value = Me.City.Value
      .Cells(9, 9).Value = Me.State.Value
      .Cells(9, 10).Value = Me.Zip.Value
      .Cells(9, 11).Value = Me.DOB.Value
      .Cells(9, 12).Value = Me.Gender.Value
      .Cells(9, 13).Value = Me.Mediciad.Value
      .Cells(9, 14).Value = Me.BillingStatus.Value
   End With
End Sub

Thank you for a quick response, sorry it took me a while to get back to you. I've been away from my desk for a nice mental break. Now I'm back to grinding at my desk lol.

I tried your code edit. It is not populating the cells. I can get it to pull up the forum to fill, But when I click on the Save Client Button nothing happens, no errors no filling of cells.

I'm at a loss.
 
Upvote 0
Add this line as shown in red
Code:
Private Sub SaveClient_Click()
   [COLOR=#ff0000]Stop[/COLOR]
   'Copy input values to sheet.
   Dim ws As Worksheet
   Set ws = Worksheets("Sheet1")
   With ws
      .Rows(9).Insert
      .Cells(9, 3).Value = Me.SDSID.Value
      .Cells(9, 4).Value = Me.LastName.Value
      .Cells(9, 5).Value = Me.FirstName.Value
      .Cells(9, 6).Value = Me.Harmony.Value
      .Cells(9, 7).Value = Me.StreetAddress.Value
      .Cells(9, 8).Value = Me.City.Value
      .Cells(9, 9).Value = Me.State.Value
      .Cells(9, 10).Value = Me.Zip.Value
      .Cells(9, 11).Value = Me.DOB.Value
      .Cells(9, 12).Value = Me.Gender.Value
      .Cells(9, 13).Value = Me.Mediciad.Value
      .Cells(9, 14).Value = Me.BillingStatus.Value
   End With
End Sub
Then click on your button, does the code window open with the word Stop highlighted in yellow?
 
Upvote 0
In that case you're button is not called SaveClient, in the VB editor show the form & double click the button, it should take you to the code module with something like
Code:
Private Sub [COLOR=#0000ff]CommandButton1[/COLOR]_Click()

End Sub
where the part in blue is the name of your button.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top