Solved: How to get values from hidden sheet

[rajesh nag]

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]

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]

AFAIAA, you're pretty much only limited to deleting a very hidden worksheet..

[lucas]

Moved to appropriate forum. You will get more help with this here Rajesh

[mikerickson]

Cross posted.
How did the advice at http://www.vbaexpress.com/forum/showthread.php?t=16425 work out for you?

[lucas]

Threads merged.......

Come on Rajesh.....try a little harder please

[rajesh nag]

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

[VBA]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[/VBA]

[Aussiebear]

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.

[rory]

You can't select a hidden sheet, so you need to change this:
[vba] Sheets("Sheet3").Select

Set FillRange = Range(("L2:L261"))
For Each Cel In FillRange
UserForm1.ComboBox2.AddItem Cel.Text
Next
[/vba] to this:
[vba]
Set FillRange = Sheets("Sheet3").Range("L2:L261")
For Each Cel In FillRange
ComboBox2.AddItem Cel.Text
Next Cel
[/vba]

[rajesh nag]

Thanks a lot rory its working fine