Posts by Bendfella

It works uou are a life saver as always.

Thank you Rory!!!!!!!

ma1h

g30 to 35 works

ma1hg30
ma1hg31
ma1hg32
ma1hg33
ma1hg34
ma1hg35

however

ma1h

9C0 to 5 does not

#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

unless u start with 9c1 but not zero

when i use this code

    Dim pref As String, myStr As String, x, LastR As Range
    pref = InputBox("Enter Prefix")
    myStr = InputBox("Enter datarange")
    If myStr = "" Then Exit Sub
    x = GetDetails(myStr)
    If IsArray(x) Then
        pref = pref & x(0)
        Set LastR = Sheets("Sheet1").[d8]
        If LastR <> "" Then Set LastR = LastR.Parent.Cells(Rows.Count, LastR.Column).End(xlUp)(2)
        LastR.Resize(Val(x(2)) - Val(x(1)) + 1) = Evaluate("""" & pref & """&row(" & x(1) & ":" & x(2) & ")")
    End If
End Sub

Function GetDetails(ByVal txt As String)
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "^(.*?)(\d+) to (\d+)$"
        If .test(txt) Then GetDetails = Split(.Replace(txt, "$1^^$2^^$3"), "^^")
    End With
End Function

thank you very much Roy, it worked.

You are our own excel guru

Good day

i want when u type in a number and the number will always have 25364 (five numbers) the letters BD must appear before the numbers.

so when u type in 25364 in the cell it must show as BD25364

the cell range is on a specific worksheet and its from Column S6 to S28

is this possible even to use vba code in a module?

Thanks for replying Roy. I took out sensitive information from the word document and it does not work well in excel.

i just thought a VBA macro auto open event could trigger "steenkampc" to the windows logon name that was signed in.

i thought it was possible because the power of excel vba is awesome

Good day

i have an excel vba macro userform that when u enter information it will update the excel sheet and then transfer it to a word document that will open and print it automatically.

everything works fine but i want the username to update on the word document and also in the vba code.

we are linked to windows server which means that when u want to log in, u press ctrl+alt+del and then type in your username and password.

i found this piece of code on the internet

Function GetUserName() As String
    GetUserName = Environ$("username")
End Function

when u type in =GetUserName() in any cell on the spreadsheet and run the macro, i it updates the name like it should

my question is where in the vba code in excel and word do i put it in so it can update that name automatically.

then i dont have to change the name manually for other people on the network.

the word file is saved under the Documents\Templates folder

here is the code that prints to the word file

Function XPrint()

  Dim objWord

  Dim objDoc

  Set objWord = CreateObject("Word.Application")

  Set objDoc = objWord.Documents.Open("C:\Users\steenkampc\Documents\Templates\Receipt.docx")
  
  objWord.Visible = True

  objDoc.PrintOut

  objWord.Quit

End Function

here is the call to XPrint

Private Sub cmd_Print_Click()

Call XPrint
cmd_close.SetFocus

End Sub

im attaching the file

No its ok Roy, I used a ListBox

All working fine now.

thanks for your help

i want the user to sign in with his name starting with surname and initial like "steenkampc"

i want the name in "Permit" sheet in cell D5 to reflect as C. Steenkamp because it uses a vlookup function in "List sheet" to return values in H5

under sheet named "Permit"

for example. you type in your employee code 123 and it must show Roy in cell A1 in Sheet1

maybe an IF function will work

if cell C2 equals steenkampc then cell D5 must be C. Steenkamp

Good day.

Please help with this vlookup function.

i have three sheets and a userform.

if you type in steenkampc on the userform, it must show C. Steenkamp taken from (sheet "List", Cell A3) in sheet "Permit"

Cell D5

thank u will try

Good morning. We have a program at work where u scan the barcode on the document then it will transfer certain information from the certificate into textboxes on the program.

I do not know in which language the program used is written but I was thinking of doing the same thing. I googled for more information but maybe the terms I am using is not correct.

Can anyone point me in the right direction where I can do more reading?

ok then the original questions is solved.

thank u Roy

hi Roy, I played around with it and now it works.

i only need to know is it possible if the windows taskbar doesnt show when i print

good morning from Namibia

I am uploading a userform where i removed most of the textboxes and code

as u type in the numbers, it automatically calculates the into textbox1. i want the same result to SHOW in textbox34.

also i am using a code i found on the internet where it captures a screenshot and prints. it is possbile for the windows taskbar not to show and make the size a little bit bigger?

Hi to all

i want to adjust it with my sheet so please help with the following

Normally when we create a userform one uses a listbox to select something e.g a name.

when the name is selected, it transfers it to a sheet. i now want when u login using a password login form that name should appear on a sheet in the excel workbook.

i am uploading such a form

i want if a name is selected e.g Jan1, the name Jan1 must also appear on sheet3 cell D5 for example.

the reason for this is there will be a userform so i dont want users to choose someone elses name when they open it.

everyone will be able to change their passwords at the beginning and when you print out your sheet at the end of the day, your name must appear there.

kind regards

Thank u Roy for your efforts

Hi Roy i an sorry the example does not work for me. What i want is when u open excel u just only see the login page. When the password is entered correctly it just open the worm ook and show a certain sheet. Use the file i uploaded first

Good morning

Please help me. i am attaching a file. when u open the excel file and click on login it asks for a username and password. u can use mine just check on the admin sheet.

i want when its correct to open sheet1.

can u provide me with the vba code please.

thanks in advance

a sum button would be nice. you always simplify things for me

Thanks Roy as always u made it so much easier.