Hide a cell so that users can't see the contents

[Malc]

Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, and I'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
.CursorLocation = adUseServer
End With
AdoConn.Open
This all works fine. The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model. Cell A65536 is
hidden - so your average user isn't going to find it. But if you do
find it, say by using the <f5> key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string - the
code is locked from viewing. But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code. Doing it the way I've done it means all
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell to make it
more difficult to find - but I can't find a way of stopping users
unhiding that sheet. Ideally I'd like to store the password somewhere
other than a cell.
Any ideas? Thanks

 

[paul.robinson]

Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False

Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul

 

[Malc]

Hi again

Yes, that's very useful and it works very well, thank you, I've just
learned a bit more! What I've also learned is that I should a bit
clearer in my question!
The macro that this code is part of sets up the DSN and then tests the
connection to make sure the adminstrator used the correct password -
if he doesn't I have a bit of error handling that issues an
appropriate message. Then I close the db connection.

So then the user gets hold of the model to run their enquiry and that
uses a different macro of course - so the ADO connection string is
repeated and needs to use the password that was supplied when the DSN
was created. Which is why I'd stored it in a cell and tried to hide
it. Storing it in a name as suggested only seems to work within the
subroutine and then it gets forgotten, so it can't be used later...

This is the code I used:

PWEntry = InputBox("Enter SunSystems Password")
Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
X1ZZZ = PWEntry
The third line's there because without it X1ZZZ doesn't pick up the
value of PWEntry, and I suspect this is part of my problem. I tried
this, which is my interpretation of what Paul suggested:
PWEntry = InputBox("Enter SunSystems Password")
Names.Add Name:="X1ZZZ", RefersTo:="SUNSYS", Visible:=False
But that doesn't set the value of X1ZZZ by itself - and I can't
hardcode the password in the VBA in any case.
But I'm probably being stupid. It's taken me years of practice to
achieve my present state of incompetency...
Many thanks again!
Malc

Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False

Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul

Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, and I'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
        .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
        .CursorLocation = adUseServer
    End With
AdoConn.Open
This all works fine.  The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model.  Cell A65536 is
hidden - so your average user isn't going to find it.  But if you do
find it, say by using the <f5> key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string - the
code is locked from viewing.  But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code.  Doing it the way I've done it means all
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell  to make it
more difficult to find - but I can't find a way of stopping users
unhiding that sheet.  Ideally I'd like to store the password somewhere
other than a cell.
Any ideas?   Thanks- Hide quoted text -

- Show quoted text -

 

[paul.robinson]

Hi
Not quite sure what you want but does the user input the password
PWEntry = InputBox("Enter SunSystems Password")

This is then stored as a name in the activeworkbook
Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Another sub is then called from this activeworkbook which uses this
name

With AdoConn
.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & [X1ZZZ]& ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") &
";" &
"DATABASE=" & Range("SUNDB") & ""
.CursorLocation = adUseServer
End With
AdoConn.Open

As long as the Activeworkbook has the name in it, you should be able
to use it to build your connection string.

regards
Paul

Hi again

Yes, that's very useful and it works very well, thank you, I've just
learned a bit more!  What I've also learned is that I should a bit
clearer in my question!
The macro that this code is part of sets up the DSN and then tests the
connection to make sure the adminstrator used the correct password -
if he doesn't I have a bit of error handling that issues an
appropriate message.  Then I close the db connection.

So then the user gets hold of the model to run their enquiry and that
uses a different macro of course - so the ADO connection string is
repeated and needs to use the password that was supplied when the DSN
was created.  Which is why I'd stored it in a cell and tried to hide
it.  Storing it in a name as suggested only seems to work within the
subroutine and then it gets forgotten, so it can't be used later...

This is the code I used:

 PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
    X1ZZZ = PWEntry
The third line's there because without it X1ZZZ doesn't pick up the
value of PWEntry, and I suspect this is part of my problem.  I tried
this, which is my interpretation of what Paul suggested:
PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:="SUNSYS", Visible:=False
But that doesn't set the value of X1ZZZ by itself - and I can't
hardcode the password in the VBA in any case.
But I'm probably being stupid.  It's taken me years of practice to
achieve my present state of incompetency...
Many thanks again!
Malc

Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False

Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Malc]

Hi Paul

You've worked out what I'm trying to do - User 1 inputs the password
and this then needs to be stored somewhere so that User 2 can run the
data query without knowing the password.

So I've done as you suggest and stored the name using

Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Then sometime later my query user opens the file and uses the
connection string in the sub that opens the db connection:

.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\utils\" & Range("dsnfile") & ";" & "UID=" &
Range("DBUSER") & ";" & "PWD=" & "X1ZZZ" & ";" & "DATABASE=" &
Range("SUNDB") & "" -
thus using the Name we stored earlier in the set up sub - but that
inserts the password as X1ZZZ, not the password that was entered at
the time the name was added, you see.

Does that make sense?

Cheers

Malc

Hi
Not quite sure what you want but does the user input the password
PWEntry = InputBox("Enter SunSystems Password")

This is then stored as a name in the activeworkbook
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Another sub is then called from this activeworkbook which uses this
name

With AdoConn
         .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
          "\" & Range("appprogdir") & "\UTILS\" & [X1ZZZ]& ";" &
          "UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") &
";" &
          "DATABASE=" & Range("SUNDB") & ""
         .CursorLocation = adUseServer
End With
AdoConn.Open

As long as the Activeworkbook has the name in it, you should be able
to use it to build your connection string.

regards
Paul

Hi again

Yes, that's very useful and it works very well, thank you, I've just
learned a bit more!  What I've also learned is that I should a bit
clearer in my question!
The macro that this code is part of sets up the DSN and then tests the
connection to make sure the adminstrator used the correct password -
if he doesn't I have a bit of error handling that issues an
appropriate message.  Then I close the db connection.

So then the user gets hold of the model to run their enquiry and that
uses a different macro of course - so the ADO connection string is
repeated and needs to use the password that was supplied when the DSN
was created.  Which is why I'd stored it in a cell and tried to hide
it.  Storing it in a name as suggested only seems to work within the
subroutine and then it gets forgotten, so it can't be used later...

This is the code I used:

 PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
    X1ZZZ = PWEntry
The third line's there because without it X1ZZZ doesn't pick up the
value of PWEntry, and I suspect this is part of my problem.  I tried
this, which is my interpretation of what Paul suggested:
PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:="SUNSYS", Visible:=False
But that doesn't set the value of X1ZZZ by itself - and I can't
hardcode the password in the VBA in any case.
But I'm probably being stupid.  It's taken me years of practice to
achieve my present state of incompetency...
Many thanks again!
Malc

On 6 Feb, 11:20, (e-mail address removed) wrote:

Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False
Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul
Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, and I'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
        .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
        .CursorLocation = adUseServer
    End With
AdoConn.Open
This all works fine.  The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model.  Cell A65536 is
hidden - so your average user isn't going to find it.  But if you do
find it, say by using the <f5> key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string - the
code is locked from viewing.  But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code.  Doing it the way I've done it meansall
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell  to make it
more difficult to find - but I can't find a way of stopping users
unhiding that sheet.  Ideally I'd like to store the password somewhere
other than a cell.
Any ideas?   Thanks- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Malc]

So near and yet so far!

By using

.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" & "UID=" &
Range("DBUSER") & ";" & "PWD=" & Names("X1ZZZ") & ";" & "DATABASE=" &
Range("SUNDB") & ""

I nearly made it - but my password came into the string as "PASSWORD"
rather than PASSWORD without the double quotes. The SQL connection
interpreted the quotes as part of the password.

Ho hum...

Hi Paul

You've worked out what I'm trying to do - User 1 inputs the password
and this then needs to be stored somewhere so that User 2 can run the
data query without knowing the password.

So I've done as you suggest and stored the name using

 Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Then sometime later my query user opens the file and uses the
connection string in the sub that opens the db connection:

.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\utils\" & Range("dsnfile") & ";" & "UID=" &
Range("DBUSER") & ";" & "PWD=" & "X1ZZZ" & ";" & "DATABASE=" &
Range("SUNDB") & "" -
thus using the Name we stored earlier in the set up sub - but that
inserts the password as X1ZZZ, not the password that was entered at
the time the name was added, you see.

Does that make sense?

Cheers

Malc

Hi
Not quite sure what you want but does the user input the password
PWEntry = InputBox("Enter SunSystems Password")

This is then stored as a name in the activeworkbook
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Another sub is then called from this activeworkbook which uses this
name

With AdoConn
         .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
          "\" & Range("appprogdir") & "\UTILS\" & [X1ZZZ]& ";"&
          "UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") &
";" &
          "DATABASE=" & Range("SUNDB") & ""
         .CursorLocation = adUseServer
End With
AdoConn.Open

As long as the Activeworkbook has the name in it, you should be able
to use it to build your connection string.

regards
Paul

Hi again
Yes, that's very useful and it works very well, thank you, I've just
learned a bit more!  What I've also learned is that I should a bit
clearer in my question!
The macro that this code is part of sets up the DSN and then tests the
connection to make sure the adminstrator used the correct password -
if he doesn't I have a bit of error handling that issues an
appropriate message.  Then I close the db connection.
So then the user gets hold of the model to run their enquiry and that
uses a different macro of course - so the ADO connection string is
repeated and needs to use the password that was supplied when the DSN
was created.  Which is why I'd stored it in a cell and tried to hide
it.  Storing it in a name as suggested only seems to work within the
subroutine and then it gets forgotten, so it can't be used later...
This is the code I used:
 PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
    X1ZZZ = PWEntry
The third line's there because without it X1ZZZ doesn't pick up the
value of PWEntry, and I suspect this is part of my problem.  I tried
this, which is my interpretation of what Paul suggested:
PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:="SUNSYS", Visible:=False
But that doesn't set the value of X1ZZZ by itself - and I can't
hardcode the password in the VBA in any case.
But I'm probably being stupid.  It's taken me years of practice to
achieve my present state of incompetency...
Many thanks again!
Malc
On 6 Feb, 11:20, (e-mail address removed) wrote:
Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False
Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul
Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, andI'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
        .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
        .CursorLocation = adUseServer
    End With
AdoConn.Open
This all works fine.  The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model.  Cell A65536is
hidden - so your average user isn't going to find it.  But if you do
find it, say by using the <f5> key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string - the
code is locked from viewing.  But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code.  Doing it the way I've done it means all
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell  to makeit
more difficult to find - but I can't find a way of stopping users
unhiding that sheet.  Ideally I'd like to store the password somewhere
other than a cell.
Any ideas?   Thanks- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Malc]

And finally...

Done it, thank you Paul for the inspiration. I realised that by using
names I was bound to get the double quotes, so I inserted code to make
a cell equal to the name and that dropped the quotes. Then I strung
"PWD=" &range("cell") which gave me what I wanted, then more code to
delete the contents of the cell.

None of this would have worked if I hadn't learned that you can have a
name equal to a string, I thought names were always applied to
ranges. For that I'm indebted to you, Paul.

Good man

Cheers

Malc

So near and yet so far!

By using

 .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" & "UID=" &
Range("DBUSER") & ";" & "PWD=" & Names("X1ZZZ") & ";" & "DATABASE=" &
Range("SUNDB") & ""

I nearly made it - but my password came into the string as "PASSWORD"
rather than PASSWORD without the double quotes.  The SQL connection
interpreted the quotes as part of the password.

Ho hum...

Hi Paul

You've worked out what I'm trying to do - User 1 inputs the password
and this then needs to be stored somewhere so that User 2 can run the
data query without knowing the password.

So I've done as you suggest and stored the name using

 Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False

Then sometime later my query user opens the file and uses the
connection string in the sub that opens the db connection:

.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") & "\" &
Range("appprogdir") & "\utils\" & Range("dsnfile") & ";" & "UID=" &
Range("DBUSER") & ";" & "PWD=" & "X1ZZZ" & ";" & "DATABASE=" &
Range("SUNDB") & "" -
thus using the Name we stored earlier in the set up sub - but that
inserts the password as X1ZZZ, not the password that was entered at
the time the name was added, you see.

Does that make sense?

Cheers

Malc

On 6 Feb, 12:42, (e-mail address removed) wrote:

Hi
Not quite sure what you want but does the user input the password
PWEntry = InputBox("Enter SunSystems Password")
This is then stored as a name in the activeworkbook
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
Another sub is then called from this activeworkbook which uses this
name
With AdoConn
         .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
          "\" & Range("appprogdir") & "\UTILS\" & [X1ZZZ]& ";" &
          "UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") &
";" &
          "DATABASE=" & Range("SUNDB") & ""
         .CursorLocation = adUseServer
End With
AdoConn.Open
As long as the Activeworkbook has the name in it, you should be able
to use it to build your connection string.
regards
Paul
Hi again
Yes, that's very useful and it works very well, thank you, I've just
learned a bit more!  What I've also learned is that I should a bit
clearer in my question!
The macro that this code is part of sets up the DSN and then tests the
connection to make sure the adminstrator used the correct password -
if he doesn't I have a bit of error handling that issues an
appropriate message.  Then I close the db connection.
So then the user gets hold of the model to run their enquiry and that
uses a different macro of course - so the ADO connection string is
repeated and needs to use the password that was supplied when the DSN
was created.  Which is why I'd stored it in a cell and tried to hide
it.  Storing it in a name as suggested only seems to work within the
subroutine and then it gets forgotten, so it can't be used later...
This is the code I used:
 PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:=PWEntry, Visible:=False
    X1ZZZ = PWEntry
The third line's there because without it X1ZZZ doesn't pick up the
value of PWEntry, and I suspect this is part of my problem.  I tried
this, which is my interpretation of what Paul suggested:
PWEntry = InputBox("Enter SunSystems Password")
    Names.Add Name:="X1ZZZ", RefersTo:="SUNSYS", Visible:=False
But that doesn't set the value of X1ZZZ by itself - and I can't
hardcode the password in the VBA in any case.
But I'm probably being stupid.  It's taken me years of practice to
achieve my present state of incompetency...
Many thanks again!
Malc
On 6 Feb, 11:20, (e-mail address removed) wrote:
Hi
You could store it in a name
Names.Add Name:="myPassword", RefersTo:="The Password", Visible:=False
Now use [myPassword] instead of Range("dsn1") in your string to get
the value "The Password".
someone with a bit of VBA can unhide the password, so still not
completely secure. Make the name fairly complicated as someone using
the same word for their own range name, say, will overwrite yours.
regards
Paul
Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, and I'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
        .ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
        .CursorLocation = adUseServer
    End With
AdoConn.Open
This all works fine.  The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model.  Cell A65536 is
hidden - so your average user isn't going to find it.  But if you do
find it, say by using the <f5> key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string -the
code is locked from viewing.  But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code.  Doing it the way I've done it means all
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell  to make it
more difficult to find - but I can't find a way of stopping users
unhiding that sheet.  Ideally I'd like to store the password somewhere
other than a cell.
Any ideas?   Thanks- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -