View Full Version : Solved: How to get values from hidden sheet
rajesh nag
11-29-2007, 08:10 PM
Hi All
I have created user from
In which i have to pull data into combobox from sheet3
and i would like hide Sheet
Can any one help me on this
Thanks
Rajesh
mikerickson
11-29-2007, 10:14 PM
Treat it like you would a visible sheet. What hasn't been working for you? If you post the code, the solution would be easier to find.
Zack Barresse
11-30-2007, 02:01 AM
AFAIAA, you're pretty much only limited to deleting a very hidden worksheet..
lucas
11-30-2007, 12:47 PM
Moved to appropriate forum. You will get more help with this here Rajesh
mikerickson
11-30-2007, 01:28 PM
Cross posted.
How did the advice at http://www.vbaexpress.com/forum/showthread.php?t=16425 work out for you?
lucas
11-30-2007, 02:35 PM
Threads merged.......
Come on Rajesh.....try a little harder please
rajesh nag
12-02-2007, 10:02 PM
I would like to hide sheet3 from where the values for combobox have been set. Hiding column is working but hiding sheet showing an error
Option Explicit
Private Sub cmdAdd_Click()
Dim strLastRow As Integer
strLastRow = xlLastRow("Adddata")
With UserForm1
Sheets("Adddata").Select
If (.TextBox7.Value <> "" And .TextBox2.Value <> "" And _
.ComboBox2.Value <> "" And .TextBox4.Value <> "" And _
.TextBox5.Value <> "" And .TextBox6.Value <> "") Then
Cells(strLastRow + 1, 1).Value = UserForm1.TextBox7.Value
Cells(strLastRow + 1, 2).Value = UserForm1.TextBox2.Value
Cells(strLastRow + 1, 3).Value = UserForm1.ComboBox2.Value
Cells(strLastRow + 1, 4).Value = UserForm1.TextBox4.Value
Cells(strLastRow + 1, 5).Value = UserForm1.TextBox5.Value
Cells(strLastRow + 1, 6).Value = UserForm1.TextBox6.Value
strLastRow = strLastRow + 1
UserForm1.ListBox1.RowSource = "Adddata!A4:f" & strLastRow
UserForm1.TextBox7.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.ComboBox2.Value = ""
UserForm1.TextBox5.Value = ""
UserForm1.TextBox4.Value = ""
UserForm1.TextBox6.Value = ""
UserForm1.ComboBox2.SetFocus
Unload Me
Else
MsgBox "Please Enter All Mandatory Fields"
End If
End With
End Sub
Private Sub cmdDel_Click()
With UserForm1.ListBox1
If (.Value <> vbNullString) Then
If (.ListIndex >= 0 And xlLastRow("Adddata") > 2) Then
Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
.RowSource = "Adddata!A4:f" & xlLastRow("Adddata")
ElseIf (.ListIndex = 0 And xlLastRow("Adddata") = 2) Then
Range(.RowSource)(.ListIndex + 1, 1).EntireRow.Delete
.RowSource = "Adddata!A2:f2"
End If
Else
MsgBox "Please Select Data"
End If
End With
End Sub
Private Sub textbox7_Change()
End Sub
Private Sub CommandButton1_Click()
CalendarFrm.Show
End Sub
Private Sub CommandButton2_Click()
CalendarFrm1.Show
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label2_Click()
End Sub
Private Sub Label3_Click()
End Sub
Private Sub ListBox1_Change()
Dim SourceRange As Excel.Range
Dim Val1 As String, Val2 As String, Val3 As String, val4 As String
If (ListBox1.RowSource <> vbNullString) Then
Set SourceRange = Range(ListBox1.RowSource)
Else
Set SourceRange = Range("Adddata!A4:f4")
Exit Sub
End If
Val1 = ListBox1.Value
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value
val4 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value
Label1.Caption = "* Fields Are Mandatory"
Set SourceRange = Nothing
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub UserForm_Initialize()
Dim FillRange As Range
Dim Cel As Range
DeleteBlankRows
DeleteBlankColumns
With Me.ListBox1
.BoundColumn = 1
.ColumnCount = 6
.ColumnHeads = True
.TextColumn = True
.RowSource = "Adddata!A4:f" & xlLastRow("Adddata")
.ListStyle = fmListStylePlain
.ListIndex = 0
End With
Sheets("Sheet3").Select
Set FillRange = Range(("L2:L261"))
For Each Cel In FillRange
UserForm1.ComboBox2.AddItem Cel.Text
Next
ComboBox2.ListIndex = 0
Set Cel = Nothing
Set FillRange = Nothing
Sheets("Adddata").Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DateFormats, DF
DateFormats = Array("m/d/yy;@", "mmmm d yyyy")
For Each DF In DateFormats
If DF = Target.NumberFormat Then
If CalendarFrm.HelpLabel.Caption <> "" Then
CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
Else: CalendarFrm.Height = 191
CalendarFrm.Show
End If
End If
Next
End Sub
Aussiebear
12-03-2007, 07:45 AM
Hello rajesh nag,
Firstly how do you follow the flow of info with all those "unnamed" textboxes, comboboxes etc. The bigger the project the worst it will become. Surely there are more appropriate names that could be applied.
Next, what is the error that you says is being shown? You didn't say what it was, so it's a bit difficult to know how to fix your issue other than to say "Have you tried to change the visibility setting within the vbe?
Finally, when posting sections of code, please consider using the vba feature as this makes the code more readable. Let us know how you get on with this issue. :friends:
You can't select a hidden sheet, so you need to change this:
Sheets("Sheet3").Select
Set FillRange = Range(("L2:L261"))
For Each Cel In FillRange
UserForm1.ComboBox2.AddItem Cel.Text
Next
to this:
Set FillRange = Sheets("Sheet3").Range("L2:L261")
For Each Cel In FillRange
ComboBox2.AddItem Cel.Text
Next Cel
rajesh nag
12-04-2007, 10:45 PM
Thanks a lot rory its working fine
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.