Calling Column by Header Name instead of Cell number

[PRABHAKAR9020]

Dear Sir/Madam,
I am enclosed here with my excel sheet name as "Prabhakar". In this sheet, sheet name as "MDB" is my data base and i want to get the solutions of following problem.

1). I have made and userform named as "TenderEntry" and when i click on seach button in userform, i get the details in useform corresponding to cells of data base with following code

>>> use code - tags <<<

Private Sub cmdTenderSearch_Click()
Dim w As Worksheet
Dim TenderNo As String
Dim LastRow As Long
Dim i As Long

TenderNo = Trim(TenderEntry.TextBox1.Value)
Set w = Sheet1

With w
LastRow = w.Range("B" & Rows.Count).End(xlUp).Row
For i = 10 To LastRow
If w.Cells(i, 2).Value = TenderNo Then
' Page-1, Tender Registration
'--------------------------------
txtTender_No = w.Cells(i, 2).Value
Me.cmbRiskAndCost = w.Cells(i, 3).Value

Query: In above code i am giving column reference number like 2,3 in cells. but if suppose i add or delete a column from the data base then i have again reassign the column number in all the place of VBA coding. i want to give Header name in cells so that every time i don't have to modify the code if i add/delete the column of data base.

2). I have made a text box in sheet1 (MDB) in which when i type of Tender No. (which is 2nd column of database) then only row of that tender no get displayed and other gets hide i.e search box as per following code

>>> use code - tags <<<

Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("MT_Data").Range.AutoFilter Field:=1, _
Criteria1:="*" & [D2] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

Query: When i protect this sheet, then this text box filed does not allow to search the data and gives error.

3). In this sheet1 (MDB) i have written the code of sheet protection but after protection the sheet, i am not able to increase the row height and column width.
>>> use code - tags <<<

Sub ProtectFormulas()
Dim strPassword As String
With Sheet1
    .Unprotect
    .Cells.Locked = False
    .Cells.SpecialCells(xlCellTypeFormulas).Locked = True
    .Protect 'AllowDeletingRows:=True

End With
End Sub
Sub UnProtectFormulas()
With Sheet1
    .Unprotect
   MsgBox "Cell Un-Protected Successfully"
End With
End Sub

Query: How to modify the VBA code for protecting cell so that it will allow to increase the row height and column width.

4). In the form there is text box "Name of Job"


Query: I want the data showing in this text box should be get wrapped so that i can scroll down if charactors length is very long.

5). In the from there is text box named "No. and Name of Techno-commercially acceptable bidders

Query: Actual data in excel corresponding to this cell is having para-change after 01 nos. and M/s Pargat Sing is in next line, but form is not showing in the same fashion as the data store, form is auto removing the next line command.

It would be great help if anyone please solve above queries.

Thanking you

Prabhakar

 

[p45cal]

1. Since you're using a Table (listobject in vba) you can use its properties etc. in your code.
First you can set a listobject variable thus:
Set lobj = w.Range("B9").ListObject
or if you know its name:
Set lobj = w.ListObjects("MT_Data")
or without referring to the sheet at all:
Set lobj = Range("MT_Data").ListObject

then the Tender No. column is:
lobj.ListColumns("Tender no.").DataBodyRange which you can run through (or using .Find would be quicker if you have a huge number of rows) thus:

For Each cll In lobj.ListColumns("Tender no.").DataBodyRange.Cells
rw = rw + 1
If cll.Value = TenderNo Then

at which point rw will contain the row number of the table (not the sheet) then you'll be able to use the listrows object of the listobject and its range:
Set myData = lobj.ListRows(rw).Range
then you can assign values from that row with the likes of:
txtNameOfJob = myData.Cells(lobj.ListColumns("Name of Job / Work").Index).Value
finishing with something like:

Set lobj = w.Range("B9").ListObject
rw = 0
For Each cll In lobj.ListColumns("Tender no.").DataBodyRange.Cells
  rw = rw + 1
  If cll.Value = TenderNo Then
    Set myData = lobj.ListRows(rw).Range
    txtRNCagainstTender = myData.Cells(lobj.ListColumns("PR no. ").Index).Value    'yes, your header has a trailing space!
    txtNameOfJob = myData.Cells(lobj.ListColumns("Name of Job / Work").Index).Value
    txtBidder_Accepted = myData.Cells(lobj.ListColumns("No. & Names of parties Qualified" & vbLf & "after technical evaluation").Index).Value 'you have line feeds in the header.
    'etc.
    'etc.
Exit For
  End If
Next cll

Not a column number in sight.

2. When you protect the sheet use UserInterfaceOnly:=True
This means that the protection is against the user, not vba code:
Sheet1.Protect userinterfaceonly:=True
This setting is independent of the other settings and persists (even through other protects/unprotects unless those specifically set the setting themselves to False/True) until the workbook is closed. So you only need to set the setting once.
However, you can include other settings there…

3. …two more settings: AllowFormattingColumns:=True and AllowFormattingRows:=True, so maybe:

Sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, userinterfaceonly:=True

4. & 5. You need to set the MultiLine property of these textboxes to True:


That's it.

 

[PRABHAKAR9020]

Dear Sir,
Thanks a lot for your valuable guidance.
my point wise status is as follows
Query-1: I am reviewing and trying to implement in my sheet based on you valuable guidance. i will revert for the same as soon as i grasp the concept.
Query-2: Regarding the code
Sheet1.Protect userinterfaceonly:=True

I applied the code in my sheet and when i click on micro which is for "protection of cell containing formula", the sheet gets protected and allow to search the data based on the text entered in textbox however as soon as i close the workbook and if put the data in textbox then i get error and i have to again click on protect micro.

Kindly help and provide the solution that textbox for searching the data may work in protected sheet and there will not be requirement of further protecting the sheet (if it has already been protected) when we open the file.

Query-3: Saved the setting as per your guidance and it is working fine. Thanks a lot.
Query-4: Saved the setting as per your guidance and it is working fine. Thanks a lot.
Query-5: Saved the setting as per your guidance and it is working fine. Thanks a lot.

Overall, thanks a lot sir. if you have time please solve my query.

Thanking You
Prabhakar

 

[p45cal]

however as soon as i close the workbook and if put the data in textbox then i get error and i have to again click on protect micro

I said as much:

and persists (even through other protects/unprotects unless those specifically set the setting themselves to False/True) until the workbook is closed.

Kindly help and provide the solution that textbox for searching the data may work in protected sheet and there will not be requirement of further protecting the sheet (if it has already been protected) when we open the file.

Sure, in the ThisWorkook code module:

Private Sub Workbook_Open()
Sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, userinterfaceonly:=True
End Sub

 

[PRABHAKAR9020]

Thanks a lot sir.