Ok,
try this
ActiveWorkbook.Sheets(UserForm1.MultiPage1.Value + 1).cells(1,1).value = "test"
So you can define the sheet and cell even if you don't select it. So you can fill your cells without activating the sheets. (or you can get the data you want)
Gollem
Look at the file, click the button and change the tabs(sheets will change).
(double click multitab and add code below)
Is this what you want?
Vba:
Private Sub MultiPage1_Change()
ActiveWorkbook.Sheets(UserForm1.MultiPage1.Value + 1).Select
End Sub
Gollem
You can drag the formule to the right, it will work for all cells.
Gollem
Hello,
I have uploaded your file with an example.
1 solution just with a formule and another with a macro(VBa). I 'm not sure how you want to do it actually.
Vba:
CSTR() converting to string
Left() taking characters from the left
format() to format the date(being sure that the first 5 char. are day and month)
=> CStr(Left(Format(ActiveSheet.Range("A2").Value, "dd/mm/yyyy"), 5))
Is this what you want?
Gollem
Super! Thank you Insomniac!
Exactly what I needed.
Laudus thanks for the comment.
Gollem
Hello,
if you want exactly the same as the save as-function in Excel, you have to create a userform in Vba and put a commondialog control on it(rigth click on the toolbox - additional controls - microsoft commondialog control "6.0").
Code behind a button:
[vba]'Initialize commondialog-box
frmFormulier.CommonDialog.DialogTitle = "Save as ..."
frmFormulier.CommonDialog.InitDir = ActiveWorkbook.Path 'start-path
frmFormulier.CommonDialog.Filter = "Excelfile|*.xls" 'Type of file
frmFormulier.CommonDialog.Filename = "" 'Standard file name
'Show type of commondialig-box open, save, ...
frmFormulier.CommonDialog.ShowSave
'Save workbook
Select Case frmFormulier.CommonDialog.Filename
Case Is <> ""
'There is a name filled in
ActiveWorkbook.SaveAs frmFormulier.CommonDialog.Filename
End Select[/vba]
I have uploaded a small example.
Gollem
Hello,
I have a global question, if any one could give me advice, hints or any useful info(links)....
I have a workbook on the network. If I open the workbook and somebody else has it already opened I receive a message(see attachement) that the file is locked for editing by 'be...' (user).
I want to know how excel knows which user the file has opened. Is there a file with the user in?
(I would like to create a code that gives me the user that has the workbook opened.)
Any suggestions?
Thanks
Hello,
The solution of Jindo(adding a column) is good. I don't think you can make a sum of text. You must convert it to a number.
I have the same solution, but instead of the substitute function I just use Value() (see attachement).
Gollem
Hello,
you propably had already the solution, you only had to delete the "range" code in your macro-code.
I've uploaded an example which colors and put a border around the selection you make(press button).
Hope this helps solving your first problem.
(Need more info for your second problem: save as...? workbook , copy line???)
Gollem
Sub Macro1()
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Interior.ColorIndex = 6 (yellow)
End Sub
Hello,
I've uploaded a simple example which updates sheet2(your hided sheet) as soon as you select it. (Try to delete a row in sheet1 and then select sheet2 for the result)
I hope this can be a start of your solution.
Gollem
Hello,
try to add this code in your VBa module:
......
Sub RemoveTopHeaders()
Range("1:" & Range("E1").End(xlDown).Row - 1).Delete
End Sub
Private Sub Workbook_Open()
Macro1
End Sub
save the workbook and open it again. Does it work now?
Gollem
Hello,
if you want for example that all inputs of column A have to be length 3, you have to use "DATA - VALIDATION - SETTINGS" (after selecting the columns) and here you can determine the text lenght of every cell with an error message if the user doesn't put in for ex. 3 characters.
I don't know if this is the idea.
Gollem
Hello,
I'm not sure what the problem is I've checked your file: c3 results 1 and g4 results 2, that's correct I think....
What's wrong with your method?
Do you want for example: 1 programming in ... and PM is ... and 1 programming in ... and PM is ...? (You want detailed information)
If this is the case I think you better use some Vba code instead of a formule.
Gollem
Hello,
it is not normal that you cannot save your workbook when your Vba code is protected. Normally it hasn't an influence(I have used it myself).
However if you have protected your workbook or sheet it could give a problem, or perhaps the file is read only?
Gollem
Could you be more specific? Which list?
Do you want to insert different pictures?
Gollem
Hello,
I have a program that opens an excel-workbook, I first check whether the file is opened by another user(open for read-write). This works fine, but I'd like to know which user has the file open with VBa code.
ex.
workbooks.open ....
if open
then msgbox "Book opened by user"
end if
So it's the same as you open an excel(with your windows explorer) on a network and you get the message that the file is already opened by the user ....
Thanks
I agree with John, why not use a simple formule or a simple macro that does the job.
Gollem
