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.
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
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
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
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
RE: Writing data to variable worksheets in Excel
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
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
RE: Writing data to variable worksheets in Excel
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
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
RE: Writing data to variable worksheets in Excel
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
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