Re: Search Sheets For Value & Return Sheet Number When Found
thankyou exactly what i required
regards johnt
Re: Search Sheets For Value & Return Sheet Number When Found
thankyou exactly what i required
regards johnt
Re: Search Sheets For Value & Return Sheet Number When Found
sorry cannot make any sense out of that at all, it returns invalid name error. i think i would prefer the userform idea if i could make it work
regards johnt
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox
any help would be greatly appreciated
regards johnt
Thanks to RichardScholler i have created a diary using multiple tabs. I would now like to create a template that i can paste into the multiple tabs. I have tried to copy and paste but the format ends up all wrong, I would also like to paste all the tabs in one go
Regards Johnt[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I dont have to paste anything all i have to do is select all sheets on the first page and then design my template on that page which will show on all pages
regards johnt
Re: Create Diary
thank you that works perfectly
regards johnt
Re: Create Diary
yes i did want to name the tabs with the dates of the year. much appreciated if you could put me in the right direction
regards johnt
Iwant to create a diary using excel. Iwant a seperate tab for each day of the the year.Is there an easy way to set this up rather than inserting individual worksheets.possibly using vba
Regards johnt
Re: Date And Timepicker Control
thankyou petlahev
i have installed the library manally and registered it in the registry and now everything is fine
once again thankyou
regards johnt
Re: Date And Timepicker Control
i don't know. the library program was automatic so i assumed it was installed to the correct directory. should i install it manually, and how
regards johnt
Re: Date And Timepicker Control
i downloaded and installed the library but the date and time picker is still not there, any ideas
regards john
i have created a workbook that has a userform with a microsoft date and timepicker control which works perfectly.the problem is when i try to open the workbook on my laptop the the user form will not open. i think this is to do with the datepicker control. in additional controls in the vba editor on my laptop the microsoft time and datepicker is not there.is there a reason why it is not there or do i have to download it and put it there
regards johnt
Re: Prevent/Stop Workbook Closing Via Top X
thankyou shosmeister but the worksheet needs to be seen
there are four worksheets in my work book but only one is seen. when i open the workbook the userform is displayed.there are several entries made on the userform which populate sheet 4. when i load sheet 4 from the userform the sheet appears for inspection. if everything is ok there is a button on the sheet which copies and saves the sheet and returns to the userform. i do not
want anybody to close the workbook from the visible sheet,only the userform
regards johnt
Re: Prevent/Stop Workbook Closing Via Top X
thanks for you help but i dont seem to be getting anywhere. ihave tried daves method which stops you closing the workbook but not from closing xl
i have tried this code to disable the exit buttons which works
Option Explicit
Public BooleanForClosing As Boolean
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If BooleanForClosing = False Then
MsgBox "Please using exit button"
Cancel = True
End If
End Sub
and then this code on the userform which normally closes the workbook and xl
but will not work when i disable the worksheet exit buttons
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
Display More
regards johnt
i am using the following code to close my userform and work book,this disables the exit button on the userform but i can still close the workbook by using the exit buttons on the sheet. can i disable the exit buttons on the sheet so the only way to close the workbook from the userform
regards johnt
Private Sub CmbClose_Click()
Dim ans As Variant
ans = MsgBox("This will close XL, save current file?", vbYesNoCancel)
If ans = vbYes Then 'save book and quit XL
ThisWorkbook.Save
Application.Quit
ElseIf ans = vbNo Then ' no save and quit XL
Application.Quit
ElseIf ans = vbCancel Then 'no save, no close , back to userform1
Exit Sub
End If
End Sub
Display More
Re: TextBox Control Lookup Code
sorry my fault
i did not save the file correctly and the code for the message box was still there
apologies johnt
Re: textbox lookup code
thankyou both,
i have eventually used the following code which works ok, but say i select 1 in the combo box it puts a name inthe text box, but i get a message box saying it cannot find 1. i have deleted the message box function from the code but it still appears
regards johnt
Private Sub CmbClkNo_Change()
Dim rng As Range
Dim sClkNum
With Sheets("CLOCK NUMBERS")
Set rng = .Range("b1", .Range("b65536").End(xlUp))
End With
sClkNum = Me.CmbClkNo.Value
If WorksheetFunction.CountIf(rng, sClkNum) > 0 Then
Set rng = rng.Find(sClkNum, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True)
Me.TxtbName.Value = rng(1, 3)
End If
End Sub
Display More
Re: textbox lookup code
thankyou xlite
i have tried your code but to no avail. just nothing happens at all, not even an error
Private Sub TxtbName_Change()
TxtbName.Value = Application.WorksheetFunction.Lookup _
(CmbClkNo, Worksheets("CLOCK NUMBERS").Range("B1:B530"), _
Worksheets("CLOCK NUMBERS").Range("A1:A530"))
End Sub
i have had a look at the find method but can,t see how i could adapt it to my needs
regards johnt
Re: textbox lookup code
still having no luck. i have altered the code slightly so it looks at a commbox number for the source,but still cannot get the code to work.it now gives me a compile error. i have had a look at the find method but would need more help with that
TextbName.Value = Application.WorksheetFunction.Lookup("CmbClkNo,("CLOCK NUMBERS")B1:B530,("CLOCK NUMBERS")A1:A530")
regards johnt
i am trying to use a look up formula to populate a textbox on a userform. this is the code but obiously does not work
any help would be greatly appreciated
regards johnt
Re: Error Setting Range Variable
dave thankyou for your reply, but i dont really understand what you mean. could you expand a little in what you are saying or point me in the right direction to find out for myself
regards johnt