Microsoft excel: Simple gui in VBA [closed]

Question

I just started to learn VBA and I am writing first GUI code right now. So I am supposed to create a userform where user can fill in many different data to compare their retirement options. I have the sheets in my workbook from which I can generate the values. However, I have no idea how to refer to the existing worksheets so that the random data entered by new user in userform will do the correct calculation and gives user a comparison sheet.

Any help would be nice!

    Sub cancelCommand_Click()
         Unload Me
        End Sub
     Sub previousCmd_Click()
      MultiPage1.Value = MultiPage1.Value - 1
     UpdateButtons
      End Sub
     Sub nextCmd_Click()
      MultiPage1.Value = MultiPage1.Value + 1
      UpdateButtons
     End Sub
     Sub finishCmd_Click()
     Worksheets("Conclusion").Activate
     Unload Me
    End Sub

Answer 1

You interact with data in workbooks by calling the ThisWorkbook object within the code of a Command button.

To test it out at it's very simplest form do the following:

Create a new Workbook

Create a new User Form

Add a text box

Add a button

Double click on the button and you will automatically be taken to something called "Private Sub CommandButton1_Click(). Code it as follows.

Private Sub CommandButton1_Click()

ThisWorkbook.Application.ActiveCell.Value = TextBox1.Value
Unload Me

End Sub

Move to any cell on your sheet and then run the user form. Type some text in to the text box and press your button. Whatever you typed in to the text box will be shown in the cell that you were just in.

Another variations that you may try instead of the ThisWorkbook.Application.ActiveCell.Value = TextBox1.Value line may also be:

ThisWorkbook.Sheets(1).Range("A2").Value="TextBox1.Value

The code above will Make Sheet1!A2 = Whatever was typed in to the text box every time. Bottom line you communicate with Excel cells via Ranges. See Range Object (Excel) for all of the different methods and properties that you can use with a Range.