Data Input Form question

[Cyberseeker]

I have a button on one of my sheets which, when clicked, creates a data input form containing fields. I enter data into the fields, hit submit, and a new record is created in my excel sheet. (so far, so good)

I would like to add to (improve) how my form works, but I cannot find where to edit the form's design. Can someone tell me where to find its code please.

[Logit]

How did you create the form ?

[Cyberseeker]

A friend created it about 10 years ago, and I can't contact him. The form pops up when clicked, is colored, and some of the fields are pre-loaded so there must be code hidden somewhere. Ive looked in "View Macros" and "Name Manager" but I can't see anything that I recognise, so Im wondering if there is somewhere else I should look?

[Logit]

Right click the Sheet tab / View Code.

On the left you should see a list of properties. Chose FORMS (double click if the tree isn't
already opened).

Right click the form name / view code.

[Cyberseeker]

Ive found a short macro that looks like it might be related.

Sub OpenHarvestForm()
 
 Application.WindowState = xlMinimized
 frmHarvest.Show
 Application.WindowState = xlMaximized
 ActiveWorkbook.Sheets("GrpRecords").Activate
 
End Sub

I think what I may be looking for is what is called here, "frmHarvest"

But where would I find it?

[Logit]

Sub OpenHarvestForm()  			'<-- sub name
 
 Application.WindowState = xlMinimized  '<-- minimzes the entire excel workbook
 frmHarvest.Show                        '<-- makes userform visible
 Application.WindowState = xlMaximized  '<-- makes  excel workbook full sized

 ActiveWorkbook.Sheets("GrpRecords").Activate  
 '^^^^ "Activate" prepares sheet GrpRecords for receiving actions such as writing data to it
 
End Sub

What you need to do is right click the sheet tab / view code.

When the VBA editor window opens you want to right click on the icon FORM (see arrow)
Then View Code.

https://www.amazon.com/clouddrive/sh...hare_link_copy


The code displayed in the large window changes how the various controls on the form function.

If you just view the form itself (so it shows in the right hand window) you can edit the controls, labels and positioning of each.

[Cyberseeker]

Thank you for your help so far. I have got into it successfully, and altered the values from ...

Private Sub ClearFormbutton_Click()
    Range("Calcs!AC11").Value = ""
    Range("Calcs!AC12").Value = ""
    Range("Calcs!AC13").Value = ""
    Range("Calcs!AC14").Value = ""
    Range("Calcs!AC15").Value = ""
    Range("Calcs!AC16").Value = ""

... to this. As you can see, I have different columns now.

Private Sub ClearFormbutton_Click()
    Range("Calcs!U11").Value = ""
    Range("Calcs!U12").Value = ""
    Range("Calcs!U13").Value = ""
    Range("Calcs!U14").Value = ""
    Range("Calcs!U15").Value = ""
    Range("Calcs!U16").Value = ""

However, when I operate it now, it continues to deposit information into column AC, as before? Am I missing doing something?

[Logit]

Very difficult to provide an accurate answer without having the entire workbook to review.

For additional assistance please post your project as an attachment so it can be downloaded. Remove any confidential information.

[Cyberseeker]

Originally Posted by Logit

Very difficult to provide an accurate answer without having the entire workbook to review.

For additional assistance please post your project as an attachment so it can be downloaded. Remove any confidential information.

Ive expended all my ideas and will need to take you up on your kind offer. Attached is Fish.xlsm

My problem pertains to the form (frmHarvest) activated from a button in G1 in the sheet, 'GrpRecords' . It creates an error in the sheet 'Calcs' by pasting data in the cells, 'AC11 to AC16' instead of 'U11 to U16'.

Also, the drop down menus in the form should display choices but they are empty. I hope this is not too much bother to you.

[Logit]

Here are a few things I've reviewed and have questions about. These questions
are not an attempt to be critical ... just need to have a better understanding of
where you were/are going with these items:

On the Calcs Sheet U11:U16 being referenced on the UserForm Initialize event for
several of the Textboxes.

Why ?

In the UserForm Initialize macro :

Country.Value
State.Value

Change to : Text Boxes

Then change the code to :

Country.Text = Sheets("Calcs").Range("U16").Value
State.Text= Sheets(:Calcs").Range("U14").Value

On UserForm but not on GrpRecords :

Denomination

Why ?

What are your thoughts why using this ?

If Denomination.Value = "" Then
Denomination.SetFocus
Else:
If Church.Value = "" Then
Church.SetFocus
Else
Web.SetFocus
End If

End If

Initially, I had alot of troubles trying to get the workbook to just load. Not certain why. It seems
to at least load ok now and I'm able to edit and review. Hopefully that will continue.

[NoSparks]

It appears to me the Order sheet column B has pretty much the same stuff.
Not knowing how you use the file have to wonder if the userform shouldn't be writing to there.

Your Auto_Open sub populates AC11:AC16 from U11:U16

Removed the ControlSource and RowSource from the text/combo box properties
Set default property of Submit button to false
Populate Country combo at Userform_Initialize
Populate State combo from County_Change
Submit button writes to next row on GrpRecords sheet, same as previous, now also writes to Calcs U11:U16
Don't think anything else changed

[Cyberseeker]

Originally Posted by NoSparks

It appears to me the Order sheet column B has pretty much the same stuff.
Not knowing how you use the file have to wonder if the userform shouldn't be writing to there.

Your Auto_Open sub populates AC11:AC16 from U11:U16

Removed the ControlSource and RowSource from the text/combo box properties
Set default property of Submit button to false
Populate Country combo at Userform_Initialize
Populate State combo from County_Change
Submit button writes to next row on GrpRecords sheet, same as previous, now also writes to Calcs U11:U16
Don't think anything else changed

Thanks, working well now. I get lost with 'initializing' and the such.

[Cyberseeker]

Originally Posted by Logit

Here are a few things I've reviewed and have questions about. These questions ...

On the Calcs Sheet U11:U16 being referenced on the UserForm Initialize event for
several of the Textboxes.

Why ?

A friend built this macro. Im not sure which textboxes he was meaning here.

Originally Posted by Logit

On UserForm but not on GrpRecords :

Denomination

Why ?

Column 'T' on GrpRecords

Originally Posted by Logit

What are your thoughts why using this ?

If Denomination.Value = "" Then
Denomination.SetFocus
Else:
If Church.Value = "" Then
Church.SetFocus
Else
Web.SetFocus
End If

End If

Sounds like it might be a plan. Can you explain how this would behave? A hypothetical example maybe?

[Logit]

Ok .. looks good. Thanks for the answers - helps me better understand the intent.

The Denomination had me confused ... when looking at the Sheet information there are
traditional denoms and also 'books', etc. Wasn't certain which direction it was supposed to go. Now I understand 'traditional denom'.

My personal preference would be to have the Calcs sheet hidden at all times to minimize the possibility of someone editing the data. Could mess things up otherwise.

Do you have any other questions we can assist with ?

[Cyberseeker]

Originally Posted by Logit

Do you have any other questions we can assist with ?

Im trying to do the upgrade myself. (good way of learning ) However, there is a fair chance Ill be back with questions.

Thanks for all the help so far.