Writing data to variable worksheets in Excel 1

[grnegg]

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.

 

[onlyadrafter]

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!

 

[palusa]

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

 

[grnegg]

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....

 

[CajunCenturion]

Perhaps grnegg, you could post the problematic code.

 

[palusa]

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

 

[grnegg]

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

 

[grnegg]

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

 

[palusa]

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

 

[ab123456]

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

 

[melone]

Active or Activate?

 

[palusa]

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

 

[grnegg]

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