User Buttons

[BullseyeThor]

Hi All

Please can someone please help me?

I have a schedule of stores that my department are planning on opening.

I want to make it easy to input data into the sheet and have placed a button at the top of the sheet and created a user form.

However I need them to work and can't figure it out.

The button on the main sheet needs to bring up the user form, from then I need to input the 3 key points of the new store so that it adds it to the sheet at the bottom of the list. The 3 bits of information are for cells A, B and C in the order going down the user form. top A, middle B and bottom C.

Then I have the add button.

How do I get these all to work?

If you need anymore information let me know and I will try and provide.

Thanks

 

[Norie]

What controls do you have on the userform?

 

[BullseyeThor]

From the main sheet which I want to click to open the user form is just a form control button.

On the user form itself I have 3 text boxes and 2 CommandButtons, one of which is cancel.

 

[Norie]

Let's say you got a command button called cmdAdd and 3 textboxes, Textbox1-3.

This is one way you can transfer the data from the userform to the worksheet.

Private Sub cmdAdd_Click()
Dim ws As Worksheet
Dim NextRow As Long

     Set ws=Worksheets("Sheet1") ' change worksheet name as needed

     With ws
         NextRow = .Range("A" & Rows.Count).End(xlUp).Row+1
          .Range("A"&NextRow).Value =Me
Textbox1.Value
          .Range("B"&NextRow).Value =Me
Textbox2.Value
           .Range("C"&NextRow).Value =Me
Textbox3.Value
    End With

End Sub

 

[BullseyeThor]

Where do I put this part of code to make it work?

Also how does it link to the button on the main spreadsheet?

and the set ws=worksheet ("sheet1") My worksheet is called Store Schedule does that matter?

 

[Norie]

This code is for a button on your userform.

It gleaning the userform module.

It's not linked to the button on the worksheet, which you described as being used to open/show the userform.

Yes - see the comment about the name.

 

[BullseyeThor]

So how do I make the user form appear to enter the data?

 

[Norie]

Click the button.

 

[BullseyeThor]

Did nothing.

I have added the code to the userform1 code page but the button I have placed on the spreadsheet is not doing anything.

Is there more code I need to input?

 

[Norie]

Thought you had code for the button on the worksheet.

Anyway, select it and right click.

Then choose either Assign Macro... or View Code.

If it's the latter you'll get a dialog, click the New button on the right.

The VBE should open and you should see this:

Private Sub CommandButton1_Click()

End Sub.

You'll see the same if you selected View Code earlier.

This is where the code to open/show the userform goes.

The code is simply this:

Userform1.Show

Now go back to Excel and click the button.