×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Writing data to variable worksheets in Excel

Writing data to variable worksheets in Excel

Writing data to variable worksheets in Excel

(OP)
My workbook contains multiple worksheets of identical format(only the names vary). I have created a form for data input. This form includes a combo box which is supposed to identify which worksheet the remaining information (inputted to the form).
Here in lies the problem. I haven't been able to figure out how to get the data to the appropriate worksheet. I have tried using the ".activate" and .deactivate" commands, however it errs.

Am I off base on this one? Please understand that I am a VBA novice who would really like to learn more.

RE: Writing data to variable worksheets in Excel

Hello,

THis is what I have created, a spreadsheet with three sheets, Sheet1, Sheet2, and Sheet3. Sheet 1 in A1:A3 has the sheet names.

A userform with a combobox and a command button. The combobox has a ROW SOURCE of SHEET1!1:3.

The code for the Userform is as follows:

Dim MY_SHEET As String
Private Sub ComboBox1_Change()
MY_SHEET = ComboBox1.Text
End Sub

Private Sub CommandButton1_Click()
Sheets(MY_SHEET).Activate
End Sub

Have also created a macro to call in the USERFORM, with the code

Sub show_userform_1()
UserForm1.Show
End Sub


When you run the SHOW USERFORM, the USERFORM appears, you select the sheet from the combobox and press the COMMAND BUTTON, the spreadsheet then goes to the relevant sheet. You will need to add your code to transfer the data over.

Has this helped or given you more problems?



 

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: Writing data to variable worksheets in Excel

grnegg,

supposing your form contains:

1) 1 ComboBox1 with values Sheet1, Sheet2, Sheet3;  
2) 2 TextBox (TextBox1, TextBox2) with the input values to store in cells H3 and F3;
3) 1 button CommandButton1 to execute the macro;

the code :

Private Sub CommandButton1_Click()
    ScreenUpdating = False
    Val1 = TextBox1: Val2 = TextBox2
    TheSheet = ComboBox1.Value
    Worksheets(TheSheet).Activate
    ActiveSheet.Range("H3") = Val1
    ActiveSheet.Range("F3") = Val2
    ScreenUpdating = True
End Sub

could work.

Hope it helps.

_LF

RE: Writing data to variable worksheets in Excel

(OP)
Both suggestions (onlyadrafter and palusa) are slight variations of what I've already tried. After further experimenting, I believe that my problem is with the "activate" command. This command doesn't appear to like my sheet name variable ("MY_SHEET" in onlyadrafter's solution and "TheSheet" in palusa's solution. Is it possible that this statement cannot accept anything other than an actual sheet name?

Could the "Goto" command be applied in this problem?

p.s. I like both solutions.....if only I could get the darn ACTIVATE command to work....

RE: Writing data to variable worksheets in Excel

Perhaps grnegg, you could post the problematic code.

RE: Writing data to variable worksheets in Excel

What do you mean with "...activate... doesn't appear to like my sheet name variable"? Do you get an error or it does not activate it?  
Though it is trivial, try to check the sheets names' syntax.
This code could tell you their names and possibly populate the combox.

Sub b()
    With ActiveWorkbook
        For i = 1 To .Sheets.Count
            Debug.Print .Sheets.Item(i).Name
            'ComboBox1.AddItem .Sheets.Item(i).Name
        Next
    End With
End Sub

Hope it helps.

_LF

RE: Writing data to variable worksheets in Excel

(OP)
Subject code is as follows: where my sheets are G-01,G-02, etc.

Private Sub ComboBox_click()
thesheet = ComboBox.Value
End Sub

Private Sub SaveButton_Click()


Worksheets(thesheet).Activate


Cell populating code (this works)
End Sub

Private Sub UserForm_Activate()
DateBox.Value = ""
DateBox.SetFocus
ComboBox.AddItem "G-01"
ComboBox.AddItem "G-02"
ComboBox.AddItem "G-03"
ComboBox.AddItem "G-04"
ComboBox.AddItem "G-05"

StartInput = False
StartFailBox = False
LoadInput = False
LoadFailBox = False

End Sub

RE: Writing data to variable worksheets in Excel

(OP)
In answer to palusa, the code returns error 9 "subscript out of range"

RE: Writing data to variable worksheets in Excel

Try then to check how many sheets you have:

Sub b()
    With ActiveWorkbook
        Debug.Print .Sheets.Count
        'For i = 1 To .Sheets.Count
        '    Debug.Print .Sheets.Item(i).Name
        '    'ComboBox1.AddItem .Sheets.Item(i).Name
        'Next
    End With
End Sub

_LF

RE: Writing data to variable worksheets in Excel

I think the problem lies in the fact that your thesheet variable is set in one subroutine, ComboBox_click() and then used in another routine, SaveButton_Click().

Try moving the thesheet= statement to the savebuttonclick,

Private Sub SaveButton_Click()
thesheet = ComboBox.Value
Worksheets(thesheet).Activate

Cell populating code (this works)
End Sub

or add the statement
dim thesheet as sheets to the very beginning of your code before you called any routines

RE: Writing data to variable worksheets in Excel

Active or Activate?

RE: Writing data to variable worksheets in Excel

Agree with ab123456: either you retrieve the Combobox value and use the Activate method for the relevant sheet in the same subroutine or you declare in the Declaration section the variable TheSheet as Public.

Reverting to my previous post, i think however that not being able to retrieve the names of the Sheets should be investigated.
I suggest you to use the Worksheets.Name.Add (not sure of the syntax) method to define G-01, G-02, etc and then use the property Sheets.Item(i).Name to populate the Combobox.

Good luck
_LF

RE: Writing data to variable worksheets in Excel

(OP)
ab123456,
Thanks a bunch. The problem was indeed caused by defining the value was set in a separate subroutine.

Grnegg

palusa,
The abovew fix also rectified the inability to pull up the worksheet names.

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources